locked
Calculated measure between two dates RRS feed

  • Question

  • Following fact table

    - Amount

    - StartDate

    - EndDate

    - SomeOtherDate

     

    If the user selects a date in the time dimension I want to build the sum of Amount where the selected date is between StartDate and EndDate.

    Like this

     

    SELECT SUM(Amount) WHERE <date> between StartDate and EndDate

     

    Is it possible to create a calculated measure like this.

     

    Thanks.

    Sunday, September 5, 2010 4:59 PM

Answers

  • You can use the range operator in your where clause eg WHERE ([Date].[Date].&[20100101] : [Date].[Date].&[20100630])
    • Proposed as answer by Darren GosbellMVP Tuesday, September 7, 2010 3:08 AM
    • Marked as answer by Raymond-Lee Monday, September 13, 2010 2:39 AM
    Sunday, September 5, 2010 10:04 PM

All replies

  • You can use the range operator in your where clause eg WHERE ([Date].[Date].&[20100101] : [Date].[Date].&[20100630])
    • Proposed as answer by Darren GosbellMVP Tuesday, September 7, 2010 3:08 AM
    • Marked as answer by Raymond-Lee Monday, September 13, 2010 2:39 AM
    Sunday, September 5, 2010 10:04 PM
  • How can I use such a where expression in a calculated member definition ?
    Monday, September 13, 2010 10:02 AM
  • Here's an example summing a measure over a particular date range (Adventure Works)

     

    WITH MEMBER [Measures].[Sales Date Range]
    AS
    '
    SUM([Delivery Date].[Date].&[101] : [Delivery Date].[Date].&[892],[Measures].[Internet Freight Cost])
    '

    SELECT [Measures].[Sales Date Range] ON 0
    FROM [Adventure Works]

    Monday, September 13, 2010 11:16 PM