The article How do you know if the Auto Date table is sufficient when using Power BI offers a glimpse into the inner workings of how Microsoft Power BI handles dates and times. Power BI creates an internal date table for you, but it doesn’t always provide the filtering and grouping requirements you need. When this happens, consider creating the date table yourself. Having the ability to create your own dataset is an advantage when working with a complex dataset.
In this tutorial, I will show you how to create a date table when the internal default table is not enough using data analysis expressions. If you are unfamiliar with date tables, I recommend that you read the linked article above before getting started with creating a date table yourself.
TO SEE: Google Workspace versus Microsoft 365: a side-by-side analysis with checklist (Tech Republic Premium)
I’m using Microsoft Power BI Desktop on a Windows 10 64-bit system. Throughout the article, I will use the terms date table and fact table to describe the date table and data tables respectively.
You can download the Microsoft Power BI demo file for this lesson.
What is a date table in Power BI?
A date table is a table of dates and other metadata about those dates. The relationship between fact tables and the date table allows end users to filter and compare data by time periods, such as months and years.
It’s best to know in advance if you plan to create a date table, because Power BI only allows one date table. If you build visuals on the internal Auto Date table and then create your own date table and mark it as such, Power BI destroys the Auto Date table and any visuals based on it.
A date table looks like any other table with a row for each date. The first column is a date/time data type column named Date. The remaining columns store metadata about each date, such as the year, quarter, month, and so on for the date, as you can see in Image A.
Image A
When you use a custom date table, you determine the date hierarchies used by your model. This seeps through in the rapid measures and other evaluations. It is difficult for a fact table to meet the requirements to be a date table:
- The date table must have a column named Date which is a date/time data type.
- The Date column must contain unique values.
- The Date column cannot contain empty or null values.
- The Date column cannot contain any missing dates — the dates must be contiguous.
If you choose to use a fact table, you can define it as the date table as follows:
- Select the table in the Fields pane.
- Right-click on the table and select Mark as Date Table, then select Mark as Date Table from the resulting menu.
This process can be overwhelming, but if you mark the fact table as a date table, Power BI builds the relationships and hierarchies based on this table. If you don’t, make the necessary relationships between the date table (fact table) and the other tables to get the same results.
Using DAX to create a date table in Power BI
When you need to create a custom date table, you can use DAX to create a calculated table. DAX is an expression language used in Analysis Services, Power BI, and Power Pivot that contains functions and operators.
You can use the DAX CALENDAR or CALEDARAUTO function to create a date table. Both return a single column table of dates.
When considering which function to use, CALENDAR needs the first and last date so that it can generate a complete list of dates. CALENDARAUTO uses existing dates in a fact table to generate a list of dates. We use CALENDAR to create a date table.
The CALENDAR function uses the following syntax:
CALENDAR(<start_date>, <end_date>)
where startdate is the first date in the resulting date table and enddate is the latest. The function returns a single column table filled with a list of dates from start date to end date and every day in between.
Now let’s use CALENDAR to create a date table in Power BI with January 1, 2000 as the start date and December 31, 2021 as
- Launch Power BI. If necessary, choose New from the File menu so that you are working with a new .pbix file. You don’t want to work in an existing .pbix file.
- Click Data in the left pane.
- On the far right, click the Write a DAX expression to create a new table option from the menu.
- complete the expression,
Table = CALENDAR (DATE (2020, 1, 1), DATE (2022, 12, 31))(Figure B).
Figure B
The function creates the new table, names the single column Date, and fills that column appropriately. It is worth noting that there are 1,096 different values or rows. However, 365 * 3 is 1.095. Power BI knows that 2020 was a leap year.
The next step is to add columns for each date component you need: week number, month number, quarter, year and so on. At this point, you can use the Add Column option to add more columns. However, it is more efficient to add them when you create the table:
Date =
ADDCOLUMNS (
CALENDAR (DATE (2020, 1, 1), DATE (2022, 12, 31)),
"Year", YEAR([Date]),
"MonthNumber", FORMAT([Date], "MM"),
"Quarter", FORMAT ([Date], "Q" ),
"DayOfWeek", FORMAT ([Date], "dddd" )
)
Figure C shows the results. The number of columns you add depends on the filtering and grouping needs of your visualizations and reports.
Figure C
For this example, the function will only create a few columns, but there are many more you’ll need. For a full list, visit Date and Time Functions (DAX) – DAX | Microsoft Learning.
This expression combines ADDCOLUMNS and CALENDAR:
- Date is the name of the resulting table.
- ADDCOLUMNS allows you to specify Date columns.
- CALENDAR creates a date table and populates the first column.
- The next four lines define the metadata columns.
At this point things get a little muddy. You need to decide whether you want to mark the custom table as a date table, as discussed earlier regarding marking a fact table as a date table. Doing so creates the custom hierarchies defined by the date table.
On the other hand, if you want Power BI to do this for you, don’t mark the table as a date table. You can create the relationships and use them instead for specialized grouping and filtering. This is one of those areas where there is no right or wrong, but knowing your data is the key to making the most efficient choice.
Remember, when you mark a fact table as a date table, Power BI removes the built-in automatic date table and any visuals you’ve built on it previously. If you deselect the date table, Power BI automatically creates a new automatic date table.
Using DAX to create a custom date table is easy if you know how to do it. There are other ways, and I’ll cover those in future articles.
