Using your own date table

You can either create a date table in Power BI or use a date table already in your data source. Many data sources are time-based and contain a date table. But if your data source doesn’t provide one, you can create one in Power BI, by using either Power Query or DAX. DAX has a built-in function called CALENDARAUTO that can generate a date table for you, which we will implement as follows:

1. Once you have your date table, you have to mark it as a date table. To mark a table as a date table, you must have a table in your model that has the following:
   1. A column with the date data type.
   1. That column must contain unique values.
   1. That column can have no blank or null rows.
   1. The column must have a contiguous date span; it cannot have any missing dates in the middle.
   1. The column must span a full year, but it does not have to be a calendar year.
2. If you have a table that meets those requirements, you can mark it as a date table by selecting the three dots (ellipses) next to the table name in the modeling view and selecting Mark as date table from the selection.

Figure 5.15 – Let’s make that a date table!

• That will bring up the Mark as date table dialog, where you can tell Power BI which column you want to use as your date column. If the column meets all of the criteria, you will get a green validation checkmark and the OK button will become active.

Figure 5.16 – Power BI will only let you pick a column that meets the requirements

Congratulations, you now have a date table!

• The next step is to ensure that all of your date columns are in a one-to-many relationship with the date table. The “one” side of the relationship must be the date table.

Now we can talk about what wonders a date table can unlock.

Date math

Power BI has some really powerful time intelligence functions that require a date table to work. These functions allow you to create calculations based on time periods such as days, months, quarters, and years.

Currently, there are 35 time-based calculations that require a date table to work. I will not list them all here, as my editor hates long, bulleted lists. If you are using a date table, then the date column in the calculation must come from the date table for these calculations to work.