Calculations that make comparisons to previous quarters or to the same period one year ago are very important in all business intelligence tools. DAX, the formula language in PowerPivot, allows you to define such calculations by means of Time Intelligence Functions.
However, in order to use these, you need to include a Date dimension table in your PowerPivot model. When you do not have access to a date table in your ICT network, you will be able to create one on the spot after reading this post.
A Date dimension table is a table of dates. It contains one row for every date that might occur in your dataset to be analyzed. Here is a quick scenario to do it.
Step 1: Start with a single date value
Open an Excel sheet, and enter the very first date that might play a role in your PowerPivot model. Let us assume this date is the 1st of January 2014. In the top left cell of your Excel sheet (cell A1) enter the label for your “Date” column. In the cell below, put the value 1/1/2014. Select the cell that contains the first date, and then drag the fill handle across the adjacent cells below the active cell. Drag it all the way down, until your column of dates is complete.
Step 2: Define your column of dates as an Excel table
Be sure to indicate that there is a header row.
Step 3: Add the table to the PowerPivot model
In the PowerPivot tab, select one of the cells of your table, and click Add to Data Model.
Step 4: Mark your table as “the Date table” in your PowerPivot model
In the PowerPivot model, you can rename your table as ‘Date’. Your Date column is automatically recognized as a Data Data Type.
Next, tell the PowerPivot model that this table, which happens to name “Date”, is actually going to be used as a Date (dimension) table in the model. From the Design tab, click Mark as Date Table and then choose the column which holds the real date values. In this example, that column also bears the name Date (how convenient from us).
Step 5: Derive other Date attributes with DAX
We’ll complete our Date dimension by defining new columns so we can slice and dice by ‘Year’, ‘Quarter’, et cetera. These columns are defined as calculated columns. Here is the list of columns with their formulas.
=SWITCH([Month], 1, “Jan”, 2, “Feb”, 3, “Mar”, 4, “Apr”, 5, “May”, 6, “Jun”, 7, “Jul”, 8, “Aug”, 9, “Sep”, 10, “Oct”, 11, “Nov”, 12, “Dec”, “Unknown month number” )
=SWITCH([WeekDayNr], 1, “Sun”, 2, “Mon”, 3, “Tue”, 4, “Wed”, 5, “Thu”, 6, “Fri”, 7, “Sat”,”Unknown day” )
Quarter =SWITCH(‘Date'[Month],1,”Q1″,2,”Q1″,3,”Q1″,4,”Q2″,5,”Q2″,6,”Q2″,7,”Q3″,8,”Q3″,9,”Q3″,10,”Q4″,11,”Q4″,12,”Q4″,”Unknown quarter”)
=’Date'[Year] & ” ” & ‘Date'[MonthNameShort]
=’Date'[Year] & ” ” & ‘Date'[Quarter]
=IF(‘Date'[WeekDay] = “Sat” || ‘Date'[WeekDay]=”Sun”,TRUE(),FALSE())
We also need a DateKey column as a unique identifier of Date rows:
=YEAR(‘Date'[Date]) & RIGHT(“0” & MONTH(‘Date'[Date]),2) & RIGHT(“0” & Day(‘Date'[Date]),2)
And here is our Date dimension:
Step 6: Adjust sort orders
The values of the numeric attributes are sorted numerically. The values of the non numeric attributes are sorted alphabetically which is fine for attributes Quarter and Year Quarter. However, for the attributes MonthNameShort, WeekDay and YearMonth, this alphabetical order doesn’t match the logical time order. Adjust the ordering of these columns and have them sorted by another column that has the correct order.
You can sort MonthNameShort by Month. The column WeekDay by can be sorted by WeekDayNr.
For the YearMonth, containing values like 2015 Jan, 2015 Feb, .. , the sorting can be done by a numeric column containting the year and month part (YYYYMM format). This YYYYMM column can quickly be derived with this formula:
=’Date'[Year] & RIGHT(0 & ‘Date'[Month],2)
You can new sort YearMonth by YYYYMM .
The new column can be hidden from the end user model. Right-click the the column name, and click Hide from Client Tools.
Your date dimension, created from a single value, is now ready for use.
More about this topic on MSDN is here.