Calculating Previous Year Marketing Calendar Values with DAX

UPDATE (9/27/2010): watch out for the mystery 53 week that occurs every 5 years or so.  formula does not account for this at all and will fail eventually.

UPDATE (10/9/2010): I have an updated post that has a resolution for handling the 53 week Marketing (Retail) Calendar setup – Round 2: Calculating Previous Year Marketing Calendar Values with DAX.

When it comes to reporting typically we will use the common calendar year that starts on January 1 and goes through December 31 with the standard months.  We might even be faced with a fiscal calendar that starts on July 1 and ends June 30 the following year.  In both of these cases we are sticking with the set days in the months and are not doing anything special.  But what happens when we start to use a custom calendar for marketing or retail purposes like a 445, 454, 544, or possibly 13 periods of 4 weeks each?  Will this have any impact on how we need to create our metrics to report on our data?  The answer – Yes.  Because in this case we are not simply evaluating September 1 of this year to September 1 of last year.  Nope.  We are actually evaluating it to September 2 of last year which is the same day of the week.

So now the challenge is how can we do this and how can we do it with PowerPivot using DAX?

Disclaimer: I cannot guarantee that this calculation and example will work the same as it did for me, so please always verify the results and add any additional checks that may be needed, this is strictly setup as a demonstration.

For this demonstration I am going to use a marketing calendar using a 454 week setup that starts with the first week in March.  To create this date dimension table I leveraged the functionality in BIDS to create a time table in my data source to generate this table for me – Creating a Time Dimension by Generating a Time Table.

Once this table was created for me in my SQL Server database I went into that table and created a new column in the table that I called PY_Date.  This column is going to contain the prior year equivalent date for the dates that are stored in this table in the PK_Date field that was generated by the wizard.  I then went ahead and populated this column with the prior year equivalent dates so we now have data that looks like this in the table (I am just displaying the two date columns here):

 

This new column is what I call the ‘silver bullet’ that will be required to reference in our DAX formula.  I am kind of blowing it by introducing this first, but I will continue on and show a couple of DAX formulas for a typical calendar and then how it will be adjusted to work with the marketing calendar.

Once we have this table setup and have some facts that we want to relate to this new time (date) dimension we go ahead and load this into PowerPivot and setup our relationship between the two tables.

Now we are ready to setup a PowerPivot PivotTable to analyze our data and create additional measures.  In this first example I place the Year Name and Month on the Rows and Sales Amount in the Values.  After this I then create a new measure to calculate the Previous Year Sales Amount leveraging the CALCULATE, DATESBETWEEN, FIRSTDATE, DATEADD, and LASTDATE functions:

PY SalesAmount =CALCULATE([Sum of SalesAmount], DATESBETWEEN(DimRetailDate[PK_Date],FIRSTDATE(DATEADD(DimRetailDate[PK_Date],-12,MONTH)),LASTDATE(DATEADD(DimRetailDate[PK_Date],-12,MONTH))))

Here are the results we end up with:

Now this works create when you have standard reporting that uses the typical set month periods.  What does it look like when we swap out the members on the Rows with the Reporting Year Name and Reporting Month values that are referencing our marketing 454 week calendar setup:

As you can see this same formula will not work.  So now we take a look at using that additional column that we setup earlier for the equivalent prior year value for our dates and we modify the original calculation slightly:

PY Sales Amount =CALCULATE([Sum of SalesAmount], DATESBETWEEN(DimRetailDate[PK_Date],FIRSTDATE(DimRetailDate[PY_Date]),LASTDATE(DimRetailDate[PY_Date])))

Now let’s take a look at how this looks with the data:

There we go.  That is what we want.  Now we are looking at equivalent dates.  So we need one measure to use with standard calendar reporting periods and a different one if we are reporting against equivalent time periods like using a 454, 445, 544, or some other type like 13 equal 4 week periods.  The secret ‘silver bullet’ is the additional previous year equivalent date column in the time (date) dimension table that is utilized to make the results we need.

Warning: As stated above I cannot guarantee that this is going to work the same in your environment and you might need to include some additional IF logic to do some additional checks to return BLANK().

Enjoy!