Blog

How to create a date table in PowerPivot with one single value

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.

150309b

Step 2: Define your column of dates as an Excel table

Be sure to indicate that there is a header row.

150309c

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.

150309d

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.

150309e

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

150309f

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.

Year
=YEAR(‘Date'[Date])

Month
=MONTH(‘Date'[Date])

Day
=Day(‘Date'[Date])

WeekDayNr
=WEEKDAY(‘Date'[Date])

MonthNameShort
=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” )

WeekDay
=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”)

YearMonth
=’Date'[Year] & ” ” & ‘Date'[MonthNameShort]

YearQuarter
=’Date'[Year] & ” ” & ‘Date'[Quarter]

IsWeekend
=IF(‘Date'[WeekDay] = “Sat” || ‘Date'[WeekDay]=”Sun”,TRUE(),FALSE())

We also need a DateKey column as a unique identifier of Date rows:

DateKey
=YEAR(‘Date'[Date]) & RIGHT(“0” & MONTH(‘Date'[Date]),2) & RIGHT(“0” & Day(‘Date'[Date]),2)

150309g

And here is our Date dimension:

150309h

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.

150309i

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:

YYYYMM
=’Date'[Year] & RIGHT(0 & ‘Date'[Month],2)

You can new sort YearMonth by YYYYMM .

150309j

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.