Power BI Date Dimension; Default or Custom? Is It Confusing?

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

If you have worked with Power BI for some time, you know that there are two types of the Date dimensions; Custom or built-in/Default. It is always confusing for people, that which date dimension is good to use, and what is the difference between these two approaches. Also based on the selection of the type of Date dimension, your DAX calculations may differ a little bit. In this post, I’ll explain all you need to know about the default and custom date dimension in Power BI and help you to choose the right one for your Power BI solution. If you like to learn more about Power BI, read the Power BI book from Rookie to Rock Star.

Power BI Default Date Dimension

The Power BI date dimension is still a bit of mystery for some people. Many people still are not aware that there is a default or built-in date dimension. This date dimension hasn’t existed at the very beginning of Power BI Desktop (which was July 2018). It came a bit after into the party. The purpose of this default date dimension is to ease the way that time intelligence calculations work in Power BI and make the whole experience easier for the user. Let’s see how this dimension works.

Power BI Creates a Default Date Dimension for every single date field in your dataset

By default (you can change the default configuration), Power BI creates a date dimension for every single date field in your dataset. The date dimension is a generic date dimension with normal fields such as; Year, Month, Quarter, Day and etc. The configuration in Power BI Desktop that allows the model to create the default date dimension is here:

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

Having this item enabled means that Power BI automatically create a hidden date table for each field in the model that has a date or date/time data type. The reason that it is not easy to find the default Date table, is that it is HIDDEN! The main reason for having this table hidden is that user will face too much confusion if you see a Date table per each date data type column. Here is a model created with the default Date dimension:

In the screenshot above you see that there are three date fields in the FactInternetSales: DueDate, OrderDate, and ShipDate. There is a date dimension TABLE for each of these fields, that you cannot see here. But if you have an option in the Power BI settings enabled, then you can see the Date hierarchy under that table, which shows there is a table behind the scene. (Note that even if you don’t see the date hierarchy in this view, it doesn’t mean that Power BI default date dimension is not created). If you want to enable the feature to SHOW you the Date hierarchy, you can do it under Options, Preview Features. and enable “Show dates as a hierarchy in the fields list”

Why a Date table for every single Date Field?

Well, now that you know, there is a default Date dimension, your second question might be: Why Power BI creates it multiple times, for each date field?! The reason is that the Date usually acts like a role-playing dimension. Sometimes it might be Due Date, sometimes Ship Date, and sometimes, Order Date. All of these options are date fields, but their values and behavior are different. If you read my post about Calculated tables, you know that one of the ways to do role-playing dimension in Power BI is to create copies of the Date dimension. This is what Power BI does behind the scene automatically. Power BI Creates a template for Date table first, and then copy it for every single date or date/time field in the model. Here is a glance at the behind the scene of the Power BI model:

Note that you cannot see the view above in the Power BI Desktop. You can use tools like Power BI Helper to get to that information though. In the screenshot above, you can see that there is a DateTableTemplate, and then three date tables copied from that template.

What does the Default Date table look like?

There are many variations of the Date dimension in the world, and you may think, what does the default Date table look like? What columns are available there and what columns are not? Here is lit of columns:

The view above can be generated with tools such as Power BI Helper. But if you are interested to see the list of these column names in the Power BI Desktop, One way is to see it when you write a DAX expression, after typing dot (.) after the Date or Date/Time field name, you get the list of fields;

This might have been the mystery for many people when they write DAX statement; What is the list of fields that comes after the dot (.) in front of a date field name? Now you know the answer;

The Date field, is NOT a field from Power BI model point of view. It is a TABLE, a hidden table, and because of that, you can choose which column in that table you want to use within your expression.

Writing Time Intelligence Calculations with the Default Date Dimension

Writing DAX expressions for the time intelligence calculations using the default date dimension is simpler. You just need to use your Date field name plus a “.[Date]” at the end. This means that you are using the [Date] field of the hidden Date table for the expression. For example, here is how a year to date calculation looks like:

If you don’t use the “.[Date]” then you won’t get the correct result, because Time Intelligence calculations need a DATE column to work with, and with the “.[Date]”, you choose the date column in the default hidden Date table.

As you can see, the calculation doesn’t work if you do not include the “.[Date]” in the expression. But if you include it, then all is good to do. Writing time intelligence based expressions using the default Date Dimension is very easy as you’ve seen here.

Default Date Dimension has a Built-in Date Hierarchy

One of the main benefits of using the default Date dimension is the built-in Date hierarchy of Year/Quarter/Month/Day it provides. Whenever you drag a Date field, Power BI automatically shows the hierarchy under visual, because there is a hidden Date field with the built-in hierarchy behind the scene.

Default Date Dimension Consumes Memory

Oh yes! Of course like any other table structures in the Power BI in-memory based structure, every date table, consumes memory. But it would do the same even if you create your custom date dimension! Whenever you do the role-playing dimension scenario you are also consuming even more memory! The main difference is that Power BI default Date dimension will be created even if you do not want to do the date-based analysis on a date field! For example, even if you don’t use DueDate in your date-based analysis, still Power BI creates a date dimension for it. You cannot stop it for one field. You have to either stop the default creation of the Date dimension for the entire model or use it for the entire model, you cannot customize it per field. But with the custom date dimension, you can.

Custom Date Dimension

I have written a blog series about creating a custom Date dimension, and you can find many other examples of creating Date dimension in Power BI. There are many variations for that. You may think if the default Date dimension is available, then why should I have a custom date dimension? Well, there are some reasons for doing that. Let’s first check how to you can use a custom date dimension.

Mark as Date Table

To use your custom Date dimension, you have to mark your table as a Date table. This is a very important step for Power BI because then It will know that the table to be used for time intelligence calculations is this table. You can, of course, have more than one Date table to be marked as the Date table (Because of the same reason of role-playing dimensions). If you have your custom Date table, here is how to mark it as a Date table; Go to Modelling tab in the Power BI Desktop, Then choose the custom Date table of yours, and select Mark as Date Table.

You have to select a full date column as the Date column of the Date Table as well.

Usually after this change, if you look at the icon of Date fields under your custom Date table, you will see them differently (without the default Date hierarchy), which shows the table now successfully marked as a Date table.

Writing Time Intelligence Calculations with Custom Date Dimension

The prerequisite for this step is to mark the table as Date Table, which we have done in the previous step, now you can write a DAX expression as easy as below:

As you can see, in this expression, we do not need “.[Date]” to get to the date field. Because there is no hidden Date table for this column, you just refer to the column to get the Date field. In fact, if you use the “.[Date]” here, you get an error, because there is no hidden Date table here.

Default or Custom Date Dimension?

Now that you know all about the default Date dimension, and custom Date dimension, is time for the main question: What is the difference?! when to choose what? The answer like many other situations is Depends! If you want to use generic date-based analysis and you want to build a model easier and quicker, then default Date dimension is very helpful in those scenarios. But if you want to do a special date-based analysis (as an example; public holiday based analysis), then custom Date dimension will give you more power. Let’s look at differences in details:

Modeling with the default Date Dimension is Much Easier

It is true that using custom Date table means taking care of relationships, marking as date table, creating the default hierarchy and etc. If you use custom Date dimension, you have to spend more time on doing all these configurations, whereas the default Date table, will take care of everything for you.

If you have a Date field, and you are not using it, Remove It!

When you have a date field, Power BI automatically creates a table for it, create the relationship of that to the main date field, and the table consumes memory! If you do not intend to use that field, then remove it to save memory. This is also important for any other fields that you do not use in the model; remove it to get better memory consumption, but it is even more important for Date fields because behind the scene you will have not just a field, but also a table.

Customized and Complex Analysis is Easier with Custom Date Dimension

While the default Date dimension is easier for generic date-based analysis, The custom Date dimension is a very powerful option when it comes to customized analysis. As an example, let’s say you want to create a date-based analysis based on public holidays. How you can do that with the default Date dimension? Well, the answer is to create a list of public holidays as a table and merge or join it to the date field, which means kind of creating your custom Date dimension. Here in this post, I explained an example of fetching public holidays for a custom Date dimension in Power BI.

2017-09-11_14h49_33

Another example is when you need more than the default hierarchy when you want to create a weekly hierarchy, financial calendar hierarchy, and many other scenarios. The default Date dimension is good for generic analysis, but not when it comes to more customization.

Common Mistake to Avoid!

One big common mistake that I’ve seen a lot, and it comes from the confusion of the default Date dimension vs custom Date table, is that we see both in one model! let’s see what I mean:

Let’s say you want to use a custom Date dimension, and you add it to your model, you create the relationship to the date field in the fact table, and then you DO NOT Mark it as a Date Table! That is where all mistakes start! When you do not mark it as Date Table, then you allow Power BI to create the default date dimension for the date field even under this table (considering that the creation of default date tables in enabled in the options, which is ON by default). As a result, you have a custom Date table, but you have also a default Date dimension for the date field under your custom date table! It means you are using extra memory twice! and your DAX expressions also becomes even more wrong like this:

This is Wrong! you will get the correct result in the visualization, but doing it this way is Wrong! because if you are going to use the default Date table, then what is the point of adding extra custom Date dimension? if you are going to use the custom Date table, then you HAVE TO mark it as Date Table.

Summary

Date dimension and it behavior in Power BI can be confusing if you don’t know about the default Date dimension, and how to use your custom Date dimension. In this post, I explaine the differences between these two, and elaborated the difference. Please let me know in the commens below if you still have any questions about the Date dimension for your Power BI solution.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

6 Comments

  • Honestly too much to make sure is all working as expected. I disable the default, and do custom 100% of the time. That way there is no weirdness down the line not working that I have to do sleuthing to figure out.

    My custom date table is in a PBIT file that I always start with so I don’t have to create from scratch each time.

    • Using default Date table has not much work to make sure it is working. It IS working unless you have a requirement that cannot be resolved with generic Date table. In those cases, then a custom table is useful for sure 🙂 and using it with a template Power BI file is always a good idea 🙂

  • Another key thing to remember is that the custom date table must have contiguous dates to have Time intelligence functions work properly. Another common gotcha is the issue when a PowerBI Date dimension table is loaded from a standard Date dimension table from a data warehouse which has an entry for an “unknown” date and/or a date for “in the future” which can lead to non-contiguous dates and prevent you from using it in your PowerBI data model.

    • Hi Adnan.
      A proper date dimension should have all dates in it, otherwise, it would not only be problematic for Power BI, but also for other tools.
      and if you use the default date dimension, whenever the value is blank it would show it to you too. If you want that to be a custom dimension you have to take care of NA record as you mentioned.

  • Since the Default date dimension, in most cases, (holidays etc) will NOT have contiguous dates, isn’t is recommendable to uses Custom. Also, since you can use CALENDARAUTO(), your DimDateTable will always have the correct date range. ??

    • The default date dimension will have the full range in it for sure. It would be the correct range, however, if you want it to start exactly at the date of transactions and finish at the day of transactions, then you can use CalendarAuto. However, I use the default one for many of my time intelligence expressions with zero issues.

Leave a Reply

Your email address will not be published. Required fields are marked *