Home > SSAS > Results compare with Last Year Week and Day (not Date) using MDX and DAX

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'  ; 

Results - MDX

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])
    )
)

Results - Tabular

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)

Categories: SSAS Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a comment