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.
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.
21 thoughts on “Power BI Date Dimension; Default or Custom? Is It Confusing?”
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.
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.
When using the Default Date Dimension the created hierarchy will be Continuous when using it in e.g. a Line chart. But when I create a Custom Date Dimension (using DAX-function CALENDAR and then mark it as Date Table) and create a Year/Quarter/Month/Day-hierarchy within it, that hierarchy is for some reason Categorical instead of Continuous. If I expand to month level and try to change X-axis from Categorical to Continuous nothing happens. I’ve tried using all possible variants for the columns (such as FORMAT([Date], “MMMM”), MONTH([Date]) etc. etc.) but nothing works. So my question is if it’s possible to create a custom date hierarchy which will be continuous in a line chart when drilling (expanding) through all levels? And how to do this?
for a Continuous axis in visual, you need to have a numeric field. Probably the custom date table that you have has the data type of Year, Month No, … as text maybe? if that is the case change it to the Whole number. and also set their default summarization to Don’t summarize. you should be able to switch to continuous then
Hi, interesting article. I’ve had an issue when I use custom date dimension, I cannot put a trend line or forecasts, only when I switch to the default date dimensión it works, is there a way to make it works with a custom date dimensión?, what am I doing wrong?.
Trend line works with the custom Date table too. You just have to have the proper sequence in your field or hierarchy. Then you get the trend even in non-date fields. for trend line to work, you just need to have a proper sequence of values. for example all years in the right order. If you have however things like YYYYMM codes, it may not work that way, as the sequence breaks apart some areas like 201012 then 201101.
Hej Reza, If we are using a Tabular Model, which have its own Calendar dimension marked as date table, will it still create an addional calendar table for all dates in our tables? BR, Line
With the live connection, Power BI doesn’t create the built-in date table. It relies on the date dimension that you have in the Tabular model.
How does the default date dimension work for fiscal years? Are you able to customize or is it best practice to always use a custom date dimension table?
The Default date dimension doesn’t have a column for Fiscal year, quarter or period. However, you can tweak your DAX calculations to do the calculation based on Fiscal values. for example, in TotalYTD or DatesYTD one of the input parameters is the YearEndDate, which can be the end date of the fiscal year for you. This would make that calculation, a fiscal calculation.
However, that said, you may still need to have Fiscal columns (year, quarter, month, week) to be part of your visuals or slicers. If that is the case, I do highly recommend creating a custom date table. Here is an example that I added fiscal columns to a custom date dimension in Power BI.
Hi and thank you for this great article!
Do you or someone happen to know how to change the language if the time x axis is set to continous?
Right now it is shown in German for example “Mär, Apr, Mai, …” but I want it to be in English like “Mar, Apr, May, …”?
Thank you for your help and all the best,
Have you tried to change the Regional Settings of the Power BI file under options to English for example?
My company has fiscal dimdate table. When I use it, the time intelligence functions does not provide me the correct result. In our dimdate, 31-Aug-2020 is Fiscal month of Sept 2020. Then, I put in a workaround and it works. First, I removed the dimdate and add in the new date table with Power BI. Then, I created a fiscal date to calendar date conversion table (as bridge table between the fact table and Power BI date table). After that, the simple time intelligence functions starts to work. I have not done extensive testing on the solution yet. Just be aware that at this point, the date (in date table) itself no longer represent the true date in the fact table. So, I hide it.
At this point, I still do not understand why the company fiscal dimdate table is not working with Power BI Time Intelligence (such as simple function like sameperiodlastyear).
I don’t have any insights on the particular fiscal date table that your organization has used. Does it have one record per date? are any dates missing? does it include the entire period? and what do you mean by the Fiscal date table? are those dates actually fiscal dates? or normal dates with fiscal columns?
without knowing the answer to all of the questions above, I can’t really tell why that date table didn’t work.
What I can tell, however, is that you can use a normal date table, and have fiscal columns in it, and it would work for any scenarios. I have a script that you can use to create that table and use it in your model.
Thank you. This has done a lot to expand my understanding of date behaviour
Does using custom date hierarchy in visuals improve performance on Direct Query?
The performance of directQuery will be depends on which queries are sent to the data source. If your queries are all DQ, then it won’t make much of a difference. If you use your date table as Dual mode instead, that can make it better for some scenarios