How to create a date table in Microsoft Power BI

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. Power BI creates an internal date table for you, but it won’t always produce the filtering and grouping requirements that you need. When this happens, you can consider creating 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.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic 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 facts table to describe the date table and data tables, respectively.

You can download the Microsoft Power BI demo file for this tutorial.

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 end consumers 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.

A date table looks like any other table with a row for every 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 Figure A.

Figure A

When you use a custom date table, you control the date hierarchies used by your model. This trickles down to the 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/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:

1. Select the table in the Fields pane.
2. Right-click the table and select Mark As Date Table and then select Mark As Date table in the resulting menu.

This process can be overwhelming, but if you mark the facts table as a date table, Power BI will build the relationships and hierarchies based on this table. If you don’t, you’ll have to create the necessary relationships between the date (facts) table and the other tables to get the same results.

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 CALEDARAUTO 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>:

1. 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.
2. Click Data in the left pane.
3. To the far right, click the Write a DAX Expression to Create a New Table option on the menu.
4. 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 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: 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’s 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 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.

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 date table, Power BI will automatically create a new Auto Date table.

Using DAX to create a custom date table is easy if you know how to do so. There are other ways, and I’ll cover those in future articles.