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.
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).
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:
If you do choose to use a facts table, you can define it as the date table as follows:
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.
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.
The CALENDAR function uses the following syntax:
CALENDAR(<start_date>, <end_date>)
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:
Figure B
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.
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:
Date =
ADDCOLUMNS (
CALENDAR (DATE (2020, 1, 1), DATE (2022, 12, 31)),
“Year”, YEAR([Date]),
“MonthNumber”, MONTH([Date]),
“Quarter”, QUARTER([Date]),
“DayOfWeek”, WEEKDAY([Date])
)
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
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:
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.
