Power BI Blog: Creating a Dynamic Calendar Table

Welcome back to this week’s Power BI blog series. This week, we are going to look at how to create a dynamic calendar table in Power BI.

We’ve noticed that we haven’t covered calendar tables on our Power BI blog, although we’ve covered this topic on other blogs (in our Power Pivot blog series and our Power Query series) we thought that it would be useful to approach the topic with Power BI in mind anyway.

When bringing data into Power BI, the dates on the data table can contain duplicate dates for transactions that occurred on the same date, and there may gaps too (e.g. public holidays and / or weekends), when there are no transactions on certain days. For example, consider the screenshot below:

If we want to use time intelligence functions in Power BI, we have to be mindful that the software requires us to create a calendar table. This is because time intelligence functions such as the TOTALYTD function will not work as intended without a calendar table.

When creating a calendar table, we should follow several rules:

• The earliest date in the calendar table must be before or equal to the earliest date in any other table (fact(ual) or dimension(al) / lookup) in the associated database
• The final date in the calendar table must be after or equal to the latest date in any other table (fact(ual) or dimension(al) / lookup) in the associated database
• Dates must be in ascending order, increasing in increments of one day, with no duplication
• There must be no gaps (omissions) in the dates (e.g. public holidays and / or weekends must all be included)
• Call this table Calendar and name the date field Date
• For all time series functions, use Calendar[Date] as the date
• For time series row and column headers in a PivotTable, always source these from the Calendar table and always make them contiguous (i.e. they may be highlighted by the mouse without using the CTRL key).

In Power BI, we can create a dynamic calendar table that will expand or shrink depending upon the dataset available.

Click on the Modeling tab then click on the ‘New Table’ option in the Ribbon:

Then, use the following formula:

Power BI will automatically generate our Calendar table:

We can then add several columns to our Calendar table to make it more user friendly: 

Of course, you can add more columns that would better fit your needs, these additional columns serve to be a good starting base.  Further (before anyone writes in!), you can create additional columns by using the ‘Edit Query’ features of Power BI too – however, we have written about this before.

From here, refreshing our data will also update our Calendar table, so that we don’t have to worry about updating our Calendar table when our data’s date range changes.

That’s it for this week, come back next week for more Power BI!

In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.