Creating a DAX data table in Power BI
June 17, 2019
The original blog post from Pragmatic Chaos
“Why on Earth would I when I have all those nifty date functions in Power BI already” – you might ask yourself. Built-in Auto/Date Time in Power BI will serve you well for simpler models with one date column. In case you want to have additional dimensions such as a fiscal year or holidays and particularly if you want to slice multiple tables by one date table, the auto is not an option. Curiously enough, I have empirically confirmed that DAX functions such as Year to date don’t function properly unless you have a date dimension in your model.
Additionally, the Auto Date/Time option creates an automatic date dimension similar to the one created in this post for every single date field in your dataset. Those tables can get pretty big in terms of storage, the more date fields you have and the bigger the range between them, the bigger the data tables – ranging up to a few hundred MB’s. They are not visible through the Power BI interface, but you can see them if you install DAX studio, a tool for executing and analyzing DAX queries. Of course, a self-made date dimension takes up storage as well but this is something you can control.
In the words of Marvin…Let’s get it on. The first step is to avoid Power BI’s automatic creation of time functions by unchecking the Auto Date/Time feature in Options (File tab) under Data Load in Power BI desktop.
Next, we will create our calendar table named Dates. Go to the modeling tab and choose New table, enter the formula below in the formula bar inserting the table name from your model instead of YourTableName:
Dates = CALENDAR ( DATE ( YEAR ( MIN ( YourTableName[Date] ) ), 1, 1
), DATE ( YEAR ( MAX ( YourTableName[Date] ) ), 12, 31 ))
Calendar function creates a single date column by taking the earliest date of your table (MIN) and creating a date from 1st of January (1, 1) of that year, and creates all the dates until 31st of December of the latest date (MAX).
Before making additional dimensions, we have to do three important things:
- Format the Dates[Date] as a date type on the modeling tab, and if you already haven’t – format as date the other date columns belonging to tables that will be connected to this Date table.
2. Mark the table as a date table in the fields pane (far right) an click on Mark as date table in the modeling tab after which you will choose the date column (the one we created).
3. Create a relationship between the two tables in the Model section > Modeling > Manage relationships
In the Manage Relationships window click New, choose the Dates table we created and it’s Date column, then the table you want to connect it to and it’s date column. The relationship should be one to one, all good here, click OK button.
Creating a relationship of date table and reference table
Now we can add new columns in the same Dates table we created by going to Data view (click little table icon on the far left) and click New Column in the modeling tab.
To create the date dimensions you need, add formulas from code sections below to the formula bar of newly created columns, one by one.
Year = YEAR( Dates[Date]) // the first column created, everything
// after the double slash is a comment in Power BI.
Month number = MONTH( Dates[Date] ) // creating month numb
Month name = FORMAT( Dates[Date], "mmmm") // formatting the month name
Week number = WEEKNUM( Dates[Date] ) // creating week number
Week day number = WEEKDAY( Dates[Date] ) // creating day number
Week day name = FORMAT( Dates[Date], "dddd" ) // formatting the day name
Almost done. One last slick move to ensure that your month and name days are sorted properly (Power BI sorts them alphabetically by default). Click the Month name column and then Sort by column, choosing month number from the list of offered values. Repeat the same for the day.
Since sorting is usually the only purpose of month and day number, you might want to hide it from report view by right-clicking the column and choosing Hide from report view.
Power BI doesn’t allow connecting multiple columns from one table with another table, so in case you have more than one date column in your fact table that you need to slice with the date table, you can create as many of so-called “role-playing” table as you need by going to New Table and entering the formula below in the formula bar:
OrderDate = ALL ( Dates )
This will copy the existing date table, after which you will connect it to the fact table in Manage relationships, as described above.
Ta-da! You got yourself a custom date table, or several of them, that you can use for filtering or creating visuals. From here you can do various date-related DAX measures or columns such as:
Today = DATE( YEAR( NOW () ), MONTH( NOW () ), DAY( NOW() ) ) //
In this community thread, you have a few examples that can get you going.
Date PBIX download link
Thanks for reading.
Matija