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.


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.

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature
If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before....
Creating maps with R and Power BI
The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you are creative and have knowledge about the...
Reading notifications
Sending monitoring alerts through Telegram
What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the...
Send mails with Azure Elastic Database Jobs
The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want similar functionality in Azure SQL...
Sorting matrices in Power BI
Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour...
The world of data is evolving
The data landscape has changed dramatically over recent years, the world of data is evolving. In the past, we mainly heard that we needed to...