locked
How do I get a "Week over Week" calculated measure to work with a date range? RRS feed

  • Question

  • I've created a calculated measure named "Week Over Week Growth %" based on the "Year Over Year Growth %" measure created by the business intelligence wizard.  It works great for a single date, but I want it to work when I select a range of dates.

    Each weekday, we want to look at the previous day's sales in comparison to the previous week.  On Mondays, we look at the whole weekend.  When I select the three days of the weekend (Fri-Sat-Sun), the calculated measure returns nothing.

    Any help would be greatly appreciated.

    Dan Colbert

    Here's the script from SSAS for the calculated measure:

    Create Member

    CurrentCube.[Order Date].[Year-Month-Day Order Date Calculations].[Week Over Week Growth %] As "NA" ;

    // Week Over Week Growth %

    (

    [Order Date].[Year-Month-Day Order Date Calculations].[Week Over Week Growth %],

    [Order Date].[Year].[Year].Members ( 1 ) : Null,

    [Order Date].[DateKey].Members,

    {[Measures].[Average Price], [Measures].[Order Qty], [Measures].[Extended Price USD], [Measures].[Total Orders]}

    )

    =

    (

    ( [Order Date].[Year-Month-Day Order Date Calculations].[Current Order Date],

    [Order Date].[Year-Month-Day].CurrentMember)

    -

    ( [Order Date].[Year-Month-Day Order Date Calculations].[Current Order Date],

    ParallelPeriod([Order Date].[Year-Month-Day].[Date], 7, [Order Date].[Year-Month-Day].CurrentMember))
    )

    /

    ( [Order Date].[Year-Month-Day Order Date Calculations].[Current Order Date],

    ParallelPeriod([Order Date].[Year-Month-Day].[Date], 7, [Order Date].[Year-Month-Day].CurrentMember)

    ) ;

    ( [Order Date].[Year-Month-Day Order Date Calculations].[Week Over Week Growth %],

    [Order Date].[Year].[Year].Members ( 0 ),

    [Order Date].[DateKey].Members,

    {[Measures].[Average Price], [Measures].[Order Qty], [Measures].[Extended Price USD], [Measures].[Total Orders]}

    ) = Null ;

    Format_String(

    ( [Order Date].[Year-Month-Day Order Date Calculations].[Week Over Week Growth %], [Order Date].[DateKey].Members)) = "Percent" ;

    Tuesday, March 10, 2009 2:15 PM

Answers

All replies