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:

Sales YTD = TOTALYTD(
    SUM(FactInternetSales[SalesAmount]),
    FactInternetSales[OrderDate].[Date])

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:

Sales YTD = TOTALYTD(
    SUM(FactInternetSales[SalesAmount]),
    DimDate[FullDateAlternateKey])

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.

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:

Sales YTD = TOTALYTD(
    SUM(FactInternetSales[SalesAmount]),
    DimDate[FullDateAlternateKey].[Date])

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.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.