Dan English's BI Blog

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

Calculating Previous Year Marketing Calendar Values with DAX

Posted by denglishbi on September 24, 2010

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.

image

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

image 

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.

image

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:

image

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:

image

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:

image

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:

image

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:

image

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!

Advertisements

3 Responses to “Calculating Previous Year Marketing Calendar Values with DAX”

  1. […] 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 […]

  2. […] Here is a good blog from Dan English talking to retail and marketing calendars and how to deal with the relevant DAX time calculations: https://denglishbi.wordpress.com/2010/09/24/calculating-previous-year-marketing-calendar-values-with-… […]

  3. We stumbled over here from a different web page and thought I might check things out.
    I like what I see so now i’m following you. Look forward to going over your web page again.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: