How to Create a Date Table in Microsoft Power BI

The article How to know if the automatic date table is appropriate when using Power BI provides an insight 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 meet the filtering and grouping needs you need. In this case you can create the date table yourself. The ability to create your own is beneficial when working with a complex data set.

In this tutorial, I’ll show you how to use data analysis expressions to create a date table when the default internal table isn’t enough. If you are unfamiliar with date tables, I recommend reading the article linked above before creating a date table yourself.

SEE: Google Workspace vs. Microsoft 365: A Parallel Analysis with 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 fact 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 that date. The relationship between fact tables and the date table allows end users to filter and compare data based on time periods such as months and years.

It’s best to know in advance if you plan to create a date table, as Power BI only allows one date table. If you create visuals on top of 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 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 called Date. The remaining columns store metadata about each date like year, quarter, month, etc. 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 is reflected in the quick actions and other evaluations. It is difficult for a fact table to meet the requirements of 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 blank or null values.
• The date column cannot contain any missing data – the dates must be contiguous.

If you choose to use a fact table, you can define it as a date table as follows:

1. Select the table in the Fields area.
2. Right-click the table and select Mark as Date Table, then select Mark as Date Table from the menu that appears.

This process can be overwhelming, but when you mark the fact table as a date table, Power BI builds the relationships and hierarchies based on that table. If you don’t do this, you must create the necessary relationships between the date table (fact table) and the other tables to get the same results.

How to use DAX to create a date table in Power BI

If 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 either the DAX CALENDAR or the CALEDARAUTO function to create a date table. Both return a single-column date table.

When considering which function to use, CALENDAR needs the first and last dates so it can create a complete list of dates. CALENDARAUTO uses existing data in a fact table to create 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 start_date is the first date in the resulting date table and end_date is the last. The function returns a single-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 with January 1, 2000 as the start date and December 31, 2021 as the to create:

1. Start Power BI. If necessary, choose New from the File menu to work with a new .pbix file. You don’t want to work in an existing .pbix file.
2. In the left pane, click Data.
3. On the far right of the menu, click Write a DAX expression to create a new table.
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 accordingly. It’s 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 creating 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 to add depends on the filtering and grouping needs of your visualizations and reports.

Figure C

In this example, the function only creates a few columns, but you may need many more. For a complete list, see Date and Time Functions (DAX) – DAX | Microsoft Learn.

This expression combines ADDCOLUMNS and CALENDAR:

• Date is the name of the resulting table.
• With ADDCOLUMNS you can specify columns for the date.
• CALENDAR creates a date table and fills the first column.
• The next four lines define the metadata columns.

It gets a bit muddy at this point. You must decide whether you want to mark the custom table as a date table, as discussed earlier in relation to marking a fact table as a date table. This 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 for specialized groupings and filters instead. This is one of those areas where there is no right or wrong, but knowing your data is 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 previously created on it. If you disable the date table, Power BI automatically creates a new automatic date table.

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