How to Create Date Tables in Power BI Tutorial

Generating Power BI Date Tables 

There are four major ways in which date tables can be generated in Power BI:

• Source Data
• Auto Date/Time
• DAX
• Power Query

Let's review each one of these methods.

Source data

When you import your data, it may already have a date table that was created in the data source. In this case, there is no need to create another date table. This date table is ready to use, so you can simply bring it into the data model, and a relationship with other tables in your data model will be created.

If the date table does not come with the source data, there are several methods for creating it.

Auto date/time

When filtering data over date periods, the auto date/time approach uses simple time intelligence based on date columns already loaded into your model. It does not, however, provide a date table that may be used to slice and dice other tables. To use this method, you must first enable Auto date/time in Power BI. Navigate to the File ribbon > Options and Settings > Options > Data Load > Current File > Time Intelligence > Enable Auto date/time. 

After the option is enabled, Power BI Desktop will create a hidden auto date/time table based on the dates in the date column. It then creates a relationship between the hidden auto date/time date column and the date column in the model. 

When there is an auto date/time table, it will not appear as a field in the Fields pane. Instead,  it can be found as an extendable drop-down with the name of the date column, as shown below. When you expand the date column, you will see a hierarchy called the Date Hierarchy, which includes Year, Quarter, Month, and Day.

This can then be used to create a visualizations:

DAX

Another method for creating a date dimension table in Power BI is to use Data Analysis Expression (DAX) methods. CalendarAuto and Calendar are frequently used to create these tables. The difference between these two functions is that the Calendar function returns a range of dates based on the start and end dates specified as parameters within the function. In contrast, the CalendarAuto function returns a range of dates that are automatically detected from the dataset. The start date is the earliest date in your dataset, and the end date is the most recent date in your dataset.

To use the Calendar function, navigate to the Table tab on the ribbon in Power BI Desktop. Select New Table, then input the DAX formula as shown below:

The above DAX function creates a date table with a date column. Other columns, such as Year, Month, Weekday, and Week of the Year, can be added to the table. To do so, select the New Column button on the ribbon and input the DAX equation for each column you want to add. In the following examples, we will write the DAX equation to get the year, month, and month numbers from the date table:

The results of the DAX equations written for all of these new columns are shown below:

Other expressions can be used to get as many additional date-related columns as you want.

We have just used DAX to create a date table. However, this method only adds your new table to the data model; you must still create relationships between your date tables and then mark your table as the data model's official date table.

Power Query

To create a date table in Power BI, use the mash-up language, often known as M-Query.

To do so, click the Transform Data button on the ribbon and then navigate to Power Query.

Right-click in the empty space of the left Queries pane to access the following drop-down menu, where you will select New Query and Blank Query.

In the blank query tab, enter the M-query to create the date table as seen below:

The #date argument indicates the earliest day in your data's start year, month, and day, and 365*7 represents the date for the next 7 years. The days, hours, minutes, and seconds are represented by #duration, and #duration(1,0,0,0) indicates 1 day, 0 hours, 0 minutes, and 0 seconds in the query above. The advantage of this approach for creating date tables over others is that it will automatically update when new data comes in, omitting the need to recreate the table.

To change the result of the M-equation from a list of dates to a table of dates, navigate to the Transform tab on the ribbon, select Convert, and then To Table.

After this has been done, you can include other date-related columns, just as we did with the DAX equation approach for creating date tables. To do this, you must first change the date column's data type to Date by selecting the icon on the left side of the column name.

After changing the data type, you may add new columns to the table by navigating to the Add Column ribbon, selecting the dropdown beneath Date, and then selecting Year or any other column you want to add.

As seen above, the date drop-down allows you to enter the Year, Month, Quarter, Week, Day, and Age.

We have now successfully used Power Query to create a date table. You may now mark your newly created date table as such after pulling it into the data model.

Methods of Generating Power BI Date Tables: A Summary

The following table summarizes the methods described before, providing use cases for each:

Marking a Table as a Date Table

After creating the date table using one of the approaches described above, the next step is to mark it as a "date table." To do so, right-click the table's name in the Fields pane and select Mark as date table.

Power BI verifies the data in the table by marking it as a date table, ensuring that the date column is of data type Date and contains unique values.

When a table is marked as a date table, the autogenerated hierarchies for the date field in the date table are deleted, but the hierarchies for other date fields in other tables remain until a relationship is established between that field and the generated date table.

Pros and cons of DAX vs Power Query

A few differences exist between the DAX and Power Query approaches to creating a date table in Power BI. The following points are the main distinctions that can be made:

Creating Relationships between Date Tables and Other Tables

Power BI relationships provide a clear understanding of how tables are linked. They demonstrate how a column typically links two or more tables and then joins the columns from the separate tables. These relationships are formed either automatically by Power BI when the data is loaded or manually. When you enter your data into Power BI, the Autodetect feature will help you establish relationships between columns with similar names.

To manually create relationships between the date table and other tables, navigate to the model tab, where the data model is placed, and then drag the relevant column from one table and drop it into the corresponding column from the other table. In the following example, the date field from the Accidents table was dragged and dropped onto the Date field from the date table.

Another option for creating the relationship is to go to the Manage Relationship ribbon in the model tab's relationships view. In this view, you can create, update, and delete relationships between tables and autodetect existing relationships.

Creating relationships with the date table propagates filters to several tables, allowing you to display accurate information in your report between the tables connected.

Below are a few illustrations of how filters are propagated from date tables to other tables. Each of these instances can be drilled down to the next level in the hierarchy (i.e., you can drill down from year to month to quarter to day, etc):

Conclusion

You have more than one option when creating a data table with Power BI, as demonstrated in this article. However, considerations such as usability, simplicity, reusability, and your requirements may influence your choice. If you are just starting with Power BI, check out this tutorial for beginners. Or, if you're serious about becoming a Power BI whiz, sign up for our Power BI fundamentals skill track and unlock some lucrative career possibilities in business intelligence.