Date tables for DAX users – Power BI and Power Pivot

Are you using Date tables in your DAX models? If not, why not? If you are using Power BI or Power Pivot then you should be using a date table in your model. Some people would argue that it is not necessary, but once you try and scale your model, without a date tale, you will be in trouble.

In this article you will learn
What is a date table
How and why to disable the load of hidden date tables
Tips for creating a date table using DAX
How to create a date table using Power Query
Why and How you should mark your date table in Power BI
What is a Date Table?

A date table is a dimension table. Used to slice, filter and group data from facts tables. It should be loaded into your model and not created with DAX when possible. Many organisations will have a date table prepared that can be used in different models. If you don’t have one of these maybe you should consider creating one. Make once use many times. The time you spend creating one will be repaid to you over and over.

A date table can be as little as 1 column of dates to a much more descriptive table showing bank holidays, quarters and so forth. The dates must span all the dates that you expect to find in your model without skipping a day. One column of dates works fine, however for more detailed time analysis, having a date table that contains details such as Month name and number, year, qtr and so on are a real benefit.

Dimension tables are used to Filter, Group and Slicer data from facts tables and as I have said, a date table is a dimensions table. But a date table also has another role and that is to initiate time intelligence functions in DAX such as SAMEPERIODLASTTEAR

For the following examples, we have two tables of data which we will load into Power BI. One table is a Sales table and the other table is a budgets table.

The sales table contains the sales values for given days along with the corresponding dates in 2019 and the budgets table contains the forecast sales for 2020 to 2023.

How and why to disable load of hidden date tables

When we load tables into Power BI that contain a date column, Power BI will automatically create hidden date tables that will allow you to carry out date comparison and time intelligence function without a full date table.

Look at the Fields list. These are the sales and Budget tables above loaded into Power BI. Both the budget table the sales table date columns have this little calendar icon beside them. This shows us that Power BI has identified the date and has create a hidden date table.

Don’t use these date fields. I would suggest you disable Power BI automatically creating date tables. There are a few reasons for this. The first reason is that once you disable them, you cannot carry out time intelligence functions on these columns, forcing you to carry them out on an actual real date table. The second reasons is performance. All these hidden tables take up space in your model and with a large model, can slow it down.

To disable Power BI creating these hidden date tables, go to File, Options and Setting and then Options. You will then have the option to edit the Global settings or the setting for the current workbook.

Under Data Load, uncheck the box for Time Intelligence – Auto date/time for new files.

You can also do this on a global setting. That way any files you work on moving forward, will not create these automatic hidden date tables. However, if you only do it on the current file, you might forget to change the setting next time you are working in Power BI and new files will contain these hidden tables.

Once you deselect this box, Power BI will not create these tables and see in your fields list, the date columns no longer have this little date icon.

How to create a date table using DAX

This is not the preferred solution to creating a date table, however, it is important as a DAX user you know what functions are available and how, when you have no other choice, you can use DAX to create a date table.

To read this full blog visit the original post https://theexcelclub.com/date-tables-for-dax-users-power-bi-and-power-pivot/