kohera-logo-regular.svg

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.

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)

 

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:

YYYYMM
=’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.

Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
2401-under-memory-pressure-featured-image
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
2402-fabric-lakehouse-featured-image
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
2319-blog-database-specific-security-featured-image
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
blog-security_1
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...