One of the most confusing topics for me when I started to learn Power BI was the Dates.

I have a date column in my dataset but why am I getting an error message when trying to use time-intelligent quick measures?  Why is my date slicer working only for one visual but not for another in my report? How do I analyze my data by weekdays or public holidays or different fiscal years? All those questions made me dig deeper into how Power BI handles dates.

The first thing you need to know is that Power BI automatically creates a separate date table for each date or datetime field you have in your dataset. Yes, if you have “Sales Date”, “Order Date” and “Ship Date” you will end up with three automatically generated date tables. Not great for the size of our data model, right?

Another thing to note is that even if you do not intend to do time-based analyses in your reports you will still get all those date tables that might bloat your data model size quite substantially. Pro tip here: in order to save memory, remove all date fields from your dataset if you are not going to use them in the analysis.

What if you only need a “Sales Date” and an “Order Date” for your analysis, but you do not want a “Ship Date”? Can you turn off the automatically generated date table for the “Ship Date” to minimize the model size? Unfortunately, no, you can’t enable or disable it at the table level. If you decide to do that it will affect all the date fields.

The configuration in Power BI Desktop that allows the model to create auto date dimension is here and enabled by default:

In the Power BI Desktop, File menu -> Option and Settings -> Options

In the Options window, under Current File, Data Load; Time Intelligence: Auto Date/Time

 

What exactly do those hidden date tables contain?

When the default date table is created, you can see the hierarchy generated under your date field in the dataset.

The default date table includes all days through the year regardless of the actual date range in your date field. For example, if the date range in your dataset is from 01/03/2019 to 01/06/2019 the auto date table will generate dates from 01/01/2019 to 31/12/2019. Again, it increases the size of your data model and might affect the performance.

Automatically generated date tables are not visible to you. If you go to the Model view in Power BI you won’t find them there. You cannot access, edit or customize them in any way.  Where can we see them then? You can find them in DAX Studio DAX Studio | DAX Studio (use this link to download) or Power BI Helper Power BI Helper – RADACAD

There is a way to see the columns’ names included in the auto date dimension in Power BI Desktop. If you start typing DAX expression containing the date field, you can see the list of the fields. Those are the names of the Date Table columns which we can use within DAX expression.

 

What is wrong with using the auto generated date dimension and why is it strongly advised to always create a custom date table?

One of my questions at the beginning of this article was:  Why am I getting an error message when using quick time – intelligent measure?

Let’s see the DAX expression when I calculate Month- to- date Total using “Date” dimension. The writing in red warns us that Power BI uses the specific member of the created date hierarchy in this expression. My calculation works, but what if I want to analyze my data based on week, day of the week, or fiscal year, in other words on any date field which is not included in an automatically generated hierarchy?

Another hurdle I faced was not being able to use one date slicer for two visuals based on two different dates – “Order Date” and “Ship Date”. And that is because each date table can only filter the table it corresponds to. So, I need two slicers, separate one for each date hierarchy. What if I have five visuals on the report each based on a different date? I will need five slicers!

To summarize the disadvantages of the auto date tables:

  1. They are hidden from your view and cannot be modified or customized
  2. Generated for every date field in every dimension table which increases the model size and affects performance
  3. Can’t be enabled or disabled at the table level
  4. Each auto date dimension table can only filter the table it corresponds to

 

In my opinion, auto date dimension tables are quite helpful for quick and simple analysis and for people who do not have knowledge of DAX or M code, but still want to use Power BI. But for more complex analyses you will definitely need to create your own customized date dimension table or import one, the process of which I will cover in my next blog.

 

 

 

The Data School
Author: The Data School