Why Create a Date Table in Power BI?

By: Collin Quiring

We have trained thousands of folks on Power BI and have worked with hundreds of companies. We always recommend a date table be created in Power BI. And we often are asked why we are so strongly recommending a date table.

Here are some of the answers to that question.

A date table:

Gives you the ability to organize your data by time – years, quarters, months, weeks, days (or even hours and minutes if you need that much detail)

Gives you the ability to aggregate data and information by time

Has a SINGLE record for each and every day

Has that SINGLE daily record for whatever time period you set (can be 1 month or 50 years)

Only has to be created one time

And can be used in multiple datasets

And can be used as a template

And can be shared with other report writers so they don’t have to create one

Allows for data consistency in analysis

Allows for the usage of DAX for Time-intelligence functions

Such as Year-over-Year information

Gives you the ability to create Fiscal Year and Fiscal Calendar in addition to the regular calendar dates

Gives you the ability to create CUSTOMER’S Fiscal Calendar into your date table

Gives you the ability to create Holiday’s and put them into the calendar – whether for your just your country, or for other countries where you have employees or business

Gives you the ability to mash up data from multiple data sources

Particularly if the Date Field(s) in the sources are the only keys available

Gives you the ability to create different criteria specific to your organization (ie: set the working days and/or set the “day of the week” number for each day)

Just to clarify though, we do NOT recommend creating a date table in Power BI and then Marking it as a Date Table. The main reason for this is that when you mark a table as the official “date table” in Power BI the tool then creates quite a few background items for you – creating date hierarchies and determining which fields are date fields. Normally, we like Power BI doing things in the background for us. However, in this case, we have found that automatic background work by Power BI tends to make date measures more difficult and the hierarchies do not always appear as expected. And, we have found the world tends to be more complex than the structure that is created automatically (particularly with Fiscal Calendars).

There are many examples of Date Tables out there if you just search for them. It is VERY important that you fully understand a date table example if you download one. You need to be sure to understand if that is a standard calendar or if that is based on a 4-5-4 or 4-4-5 or other calendar method that your organization uses.

What kind of post would this be if we didn’t provide a simple example ourselves for you to use?

To use the following as a Date Table in your own Power BI report(s) all you need to do is create a blank query and then go to the Advanced Editor and paste this entire query into that editor (replacing whatever else is in there already). This example does NOT have a fiscal year involved.

Once you have pasted this into the Advanced Editor, change the two dates to be the date range that you want. The StartDate is currently set to 01/01/2018 and the EndDate is set to 12/31/2030.

After changing those dates, select “Done” at the bottom of Advanced Editor and you now have your Date Table.

Copy the code for the Advanced Editor from here to the end of the page.