Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for October 9th, 2010

Round 2: Calculating Previous Year Marketing Calendar Values with DAX

Posted by denglishbi on October 9, 2010

So a couple of weeks back I did the posting Calculating Previous Year Marketing Calendar Values with DAX where I talked about a solution for getting at previous (last) year values in DAX with Marketing (Retail) Calendars.  For the most part the solution worked, but as I looked at the results a bit closer I noticed that it eventually broke down and failed.  Why is this?

Well the answer should have been obvious and I have ran into this before, but eventually these 445, 454, 544, or 13 period calendars run into a small issue that they need to deal with.  The issue is that these calendars only deal with 364 days (13 periods x 4 weeks x 7 days = 364 days).  So there is an extra day that we eventually need to deal with and then we run into the leap year and all of this eventually creates the mysterious week 53!

This situation is documented here on the National Retail Federation web site “4-5-4 Calendar”:

An Explanation of the 53-Week Year

Dividing the retail calendar into 52 weeks of seven days each, or 364 days, leaves an extra day each year to be accounted for. As a result every five to six years a week is added to the fiscal calendar.

If I go back to my original solution and scroll a little further down in my PivotTable I would have seen this since in Feb 2008 we run into a leap year.  This eventually causes the solution to bomb out when we get into the 2009 and 2010 comparisons and so on.

image

So, now what do we do?  Time to regroup and look for a solution to work around this.  After some brainstorming I realized that we just needed to expand on the original solution.  This means that we need to tell each time frame that we are dealing with how it relates to the previous period.  So not only at the day level like I originally setup, but also for the week, month (period), quarter, and year.  So we go back into our table and setup some additional columns:   

image

And then populate these dates with values:

image

Make a few tweaks to the calculation to evaluate the different time frames and reference the date ranges that we just defined (I don’t show it, but we also updated the table in the PowerPivot Window to include these new columns into our model):

image

PY Sales Amount =

IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Year])) = 1,
CALCULATE([Sum of SalesAmount], DATESBETWEEN(DimRetailDate[PK_Date],
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Quarter_Of_Year])) = 1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Month_Of_Year])) = 1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Week_Of_Year]))=1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Day_Of_Year]))=1,
FIRSTDATE(DimRetailDate[PY_Date]),
FIRSTDATE(DimRetailDate[PY_Reporting_Week_Start_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Month_Start_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Quarter_Start_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Year_Start_Date])),
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Quarter_Of_Year])) = 1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Month_Of_Year])) = 1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Week_Of_Year]))=1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Day_Of_Year]))=1,
FIRSTDATE(DimRetailDate[PY_Date]),
FIRSTDATE(DimRetailDate[PY_Reporting_Week_End_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Month_End_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Quarter_End_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Year_End_Date]))))
,BLANK())

And now we go back and take a look at our results:

image

Tada! We are back in business. And this will work at the other levels as well (here we use the Reporting Year and Quarter):

image

So now we are back in business. Just needed to expand out the original solution into each of the different time frames that we wanted to analyze and make sure that we have the previous year mappings setup according to our business rules in the table we are referencing.

Advertisements

Posted in PowerPivot | Tagged: , , | 2 Comments »