Results compare with Last Year Week and Day (not Date) using MDX and DAX
It is one of the most common scenarios in retail domain to have a business analysis of comparing current day results with last year same week and same day.
Here would like to discuss a simple approach to this scenario using MDX and DAX.
MDX:
Using PARALLELPERIOD, we can create a calculated measure “LastYearWeekDay – Internet Sales Amount”. Here main key is to understand ParallelPeriod function
SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS , NON EMPTY { [Date].[Calendar Weeks].[Calendar Week] , [Date].[Calendar Weeks].[Day Number Of Week] } ON ROWS FROM ( SELECT {[Date].[Calendar Weeks].[Calendar Week].&[27]&[2005] } ON COLUMNS FROM [Adventure Works] ) GO WITH MEMBER [Measures].[LastYearWeekDay - Internet Sales Amount] AS (parallelperiod([Date].[Calendar Weeks].[Calendar Year], 1,[Date].[Calendar Weeks].currentmember),[Measures].[Internet Sales Amount]) , FORMAT_STRING = "Currency" SELECT {[Measures].[Internet Sales Amount],[Measures].[LastYearWeekDay - Internet Sales Amount]} ON COLUMNS , NON EMPTY { [Date].[Calendar Weeks].[Day Number Of Week] } ON ROWS FROM ( SELECT { [Date].[Calendar Weeks].[Calendar Week].&[27]&[2006] } ON COLUMNS FROM [Adventure Works] )
Created a Calculated measure in cube for analysis using Excel and results as
CREATE MEMBER CURRENTCUBE.[Measures].[LastYearWeekDay - Internet Sales Amount] AS (parallelperiod([Date].[Calendar Weeks].[Calendar Year], 1,[Date].[Calendar Weeks].currentmember),[Measures].[Internet Sales Amount]), FORMAT_STRING = "Currency", FORE_COLOR = 255 /*Red*/ , FONT_SIZE = 10, VISIBLE = 1 , DISPLAY_FOLDER = 'Last Year Sales' , ASSOCIATED_MEASURE_GROUP = 'Internet Sales' ;
DAX:
Implementing this scenario using DAX is relatively simple using the date dimension values and created a calculated measure as
LastYearWeekDay - Internet Total Sales:=CALCULATE( [Internet Total Sales] , FILTER( ALL( 'Date' ) , 'Date'[Calendar Year] = MAX( 'Date'[Calendar Year] ) - 1 && 'Date'[Week Of Year] = MAX( 'Date'[Week Of Year] ) && 'Date'[Day Number Of Week] = MAX('Date'[Day Number Of Week]) ) )
Points to consider:
1. Using DAX approach, we don’t get correct Grand and Sub totals (which is a separate topic on its own and may discuss later as time permits)
2. Having a great dimensional modeling with MDX and DAX skills solves these scenarios very easily
3. We can easily extend this scenario to include for any period (in this case week is the period)