The article How to know if the Auto Date table is adequate when using Power BI provides a look into the inner workings of how Microsoft Power BI handles dates and times. It’s the quickest and easiest option because you do nothing at all. Although Power BI will create an internal date table for you, it won’t always produce the filtering and grouping requirements you need. When this happens, you can create the date table yourself. Having the skill to create your own is an advantage when you’re working with a complex dataset.
In this tutorial, I’ll show you how to create a date table when the internal default table isn’t adequate using Data Analysis Expressions. If you’re not familiar with date tables, I recommend that you read the linked article above before you tackle creating a date table yourself.
I’m using Microsoft Power BI Desktop on a Windows 11 64-bit system. Throughout the article, I will use the terms date table and facts table to describe the date table and data tables, respectively, because the terms date and data are too similar and might be confused.
You can download the Microsoft Power BI demo file for this tutorial.
Featured Partners: BI Software
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 facts tables and the date table allows users to filter and compare data by time periods, such as months and years.
It’s best to know up front if you plan to create a date table because Power BI allows only 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 will destroy the Auto Date table and all visuals based on it.
SEE: Explore this side-by-side analysis of Google Workspace vs. Microsoft 365.
A date table looks like any other table with a row for every date. The first column is a date and 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 (Figure A).
When you use a custom date table, you control the date hierarchies used by your model. This trickles down to quick measures and other evaluations. It’s difficult for a facts table to meet the requirements for being a date table:
- The date table must have a column named Date that’s a date and time data type.
- The Date column must contain unique values.
- The Date column can’t contain blank or null values.
- The Date column can’t have missing dates — the dates must be contiguous.
If you do choose to use a facts table, you can define it as the date table as follows:
- Select the table in the Data pane.
- Right-click the table, and select Mark As Date Table. Then, select Mark As Date table in the resulting menu.
When you mark a facts table as a date table, Power BI will build the relationships and hierarchies based on this table. Otherwise, you’ll have to create the necessary relationships between the date (facts) table and the other tables to get the same results.
SEE: Here’s how to create a dashboard in Power BI.
How to use 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 includes functions and operators.
You can use either the DAX CALENDAR or CALENDARAUTO function to create a date table. Both return a single-column table of dates.
When considering which function to use, CALENDAR requires the first and last dates so it can generate a full list of dates. CALENDARAUTO uses existing dates in a facts table to generate a list of dates. We’ll use CALENDAR to create a date table.
How to use CALENDAR to create a date table
The CALENDAR function uses the following syntax:
Where start_date is the first date in the resulting date table and end_date is the last. The function will return a one-column table populated 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 using January 1, 2000, as start_date and December 31, 2021, as end_date:
- Launch Power BI. If necessary, choose New from the File menu, so you’re working with a new .pbix file. You don’t want to work in an existing .pbix file.
- Click Table View in the left pane.
- If necessary, click the Home menu.
- To the far right, click the Write a DAX Expression to Create a New Table option on the menu.
- Power BI provides the first component, Table =. Complete the expression, Table = CALENDAR (DATE (2020, 1, 1), DATE (2022, 12, 31)) (Figure B).
- Click Commit, the checkmark to the left.
The function creates the new table, names the single column Date and populates that column appropriately. It’s worth noting that there are 1,096 distinct values, or rows. However, 365 * 3 is 1,095. Power BI knows that 2020 was a leap year.
How to use CALENDAR to create a date table and populate its fields
The next step is to add columns for each date component that you’ll need for sorting and filtering: week number, month number, quarter, year and so on. At this point, you can use the Add Column option to add more columns. However, you don’t have to. It’s more efficient to add all of those columns when you create the table:
CALENDAR (DATE (2020, 1, 1), DATE (2022, 12, 31)),
Figure C shows the results. The number of columns you add depends on the filtering and grouping needs of your visualizations and reports.
SEE: Learn how to use Power BI in Outlook and Office for data storytelling.
For this example, the function creates only a few columns, but there are many more you might need. For a complete list, visit Date and time functions (DAX) – DAX | Microsoft Learn.
This expression combines ADDCOLUMNS and CALENDAR:
- Date is the name of the resulting table.
- ADDCOLUMNS lets you specify columns for Date.
- 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 must decide whether to mark the custom table as a date table, as discussed earlier in reference to marking a facts table as a date table. Doing so will create the custom hierarchies defined by the date table.
SEE: Follow along in this guide to add a calculated column in Microsoft Power BI.
On the other hand, if you’d like Power BI to do this for you, don’t mark the table as a date table. You can create the relationships and use it for specialized grouping and filtering instead. This is one of those areas where there’s no right or wrong, but knowing your data is key to making the most efficient choice.
Remember: When you mark a facts table as a date table, Power BI removes the built-in Auto Date table and any visuals you previously built on it. If you deselect the manual date table, Power BI will automatically create a new Auto Date table.
Subscribe to the Developer Insider Newsletter
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays