In almost every PowerBI report at some point you will require time intelligence. You might want to display information on a timeline or create time based measures using DAX time intelligence functions.
For that to work properly it’s a best practice to always create your own Date (or Calendar) table in the Power BI report, and not to rely on the Auto date/time for new files option:
Although this option Auto date/time in Power BI Desktop – Power BI | Microsoft Docs creates a hidden auto date/time table for each date column it is highly recommended to disable it because of performance reasons.
So you need to create your own Date table, that needs to follow these rules:
- It must have a column of data type date (or date/time)—known as the date column.
- The date column must contain unique values.
- The date column must not contain BLANKs.
- The date column must not have any missing dates.
- The date column must span full years. A year isn’t necessarily a calendar year (January-December).
- The date table must be marked as a date table.
See: Create date tables in Power BI Desktop – Power BI | Microsoft Docs
One way of creating such a table is to use a script similar to this one:
The problem however is that by using the CALENDARAUTO() function in this way, it will be a big table, because it will be based on all Date columns found in your complete data model:
CALENDARAUTO function (DAX) – DAX | Microsoft Docs
- The date range is calculated as follows:
- The earliest date in the model which is not in a calculated column or calculated table is taken as the MinDate.
- The latest date in the model which is not in a calculated column or calculated table is taken as the MaxDate.
- The date range returned is dates between the beginning of the fiscal year associated with MinDate and the end of the fiscal year associated with MaxDate.
So if there are dates in your model, for example an Employee birthdate, it will be taken into account so the Date table contains it.
There’s a better way to do this. The following script is an example of that:
In this script the Date table is built using the employees[employmentDate] date, which should be the date field in your Fact table in your star/snowflake data model. Other dates are not taken into account, and so this Date table has a smaller size but it still serves its purpose.
Feel free to have a look at my example here: blog/DateTable at master · srenders/blog (github.com)
There are much better examples available, so you might want to checkout these:
The point I want to make is simply, although CALENDARAUTO() is a very easy function to use, it might create a bigger Date table then required.