# How do I get a "Week over Week" calculated measure to work with a date range? • ### 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

• Hi,

The problem is if more than one item from the [Year-Month-Day] hiearchy is selected, then there is no unique CurrentMember, so the calculation fails.
http://sqlblog.com/blogs/mosha/archive/2005/11/18/writing-multiselect-friendly-mdx-calculations.aspx

Frank
• Proposed as answer by Wednesday, March 11, 2009 8:47 AM
• Marked as answer by Wednesday, March 11, 2009 5:45 PM
Wednesday, March 11, 2009 8:47 AM

### All replies

• Hi,

The problem is if more than one item from the [Year-Month-Day] hiearchy is selected, then there is no unique CurrentMember, so the calculation fails.
• 