Generic Previous Period MDX Calculation Measure

I had recently had a requirement to create a Calculated Member or Calculation for a generic previous period measure. The user wanted to use the same calculation with a number of different date cube dimensions. For  example, one may want to see previous value of Internet Sales for Adventure works over OrderDate or for Delivery Date using the same calculated member for convenience and clarity. In addition, you want to display something intuitive and user-friendly for when the value is not available (you are looking at the first dimension level member). Brief googling came to no avail, so I decided to blog the solution I created, which I thought may come pretty handy for those not particularly hot on MDX.

The idea is to use coalesceEmpty MDX function:

WITH 
 MEMBER [Measures].[Previous Internet Sales Amount] AS
 coalesceEmpty(
 ([Date].[Calendar].PrevMember, [Measures].[Internet Sales Amount]),
 ([Delivery Date].[Calendar].PrevMember, [Measures].[Internet Sales Amount]),
'Not Avaiable'
              ), FORMAT_STRING='Currency'
SELECT 
 {[Measures].[Internet Sales Amount],
 [Measures].[Previous Internet Sales Amount]} ON COLUMNS,
{[Delivery Date].[Calendar].[Calendar Year]} ON ROWS
FROM [Adventure Works]

…which results in the following:

Previous Internet Sales over Delivery Date

Now, the same calculated member can be used with a different date dimension, for here’s an example with Order Date (aka “Date” in Adventure Works cube):

WITH 
 MEMBER [Measures].[Previous Internet Sales Amount] AS
 coalesceEmpty(
 ([Date].[Calendar].PrevMember, [Measures].[Internet Sales Amount]),
 ([Delivery Date].[Calendar].PrevMember, [Measures].[Internet Sales Amount]),'Not Avaiable'
 )
, FORMAT_STRING='Currency'
SELECT 
 {[Measures].[Internet Sales Amount],
 [Measures].[Previous Internet Sales Amount]} ON COLUMNS,
{[Date].[Calendar].[Calendar Year]} ON ROWS
FROM [Adventure Works]

…with results in slightly different figures:

Using Order Date (Called "Date" in Adventure works)

Obviously this calculated member is very simple but the idea remains the same for any previous period calculation. Naturally it makes more sense to use it as a calculation rather than a session-scope member:

Using generic previous period MDX as a calculation

Hope this is useful.

If you would like an alternative solution, have a look at this one, by Alex Whittles from PurpleFrog. There are a few other ones, too, using IIF function or Utility dimension, but I did not like them as much.

Happy MDXing!

 

Leave a Reply

Your email address will not be published. Required fields are marked *