A calendar table is an obligatory part of a good data model. Without a calendar table it is not possible to easily filter multiple fact tables from one date dimension. In addition, it requires a lot of management if you have to keep definitions of the date in several tables. This article explains how to create a calendar table in Power BI Desktop in 5 minutes.
Note: There are more ways to load a date calendar. Perhaps you already have it available in your Datawarehouse? Then it is preferable to use that one in your data model. So first check with the IT department whether a calendar table is already available. Is there no calendar available? Don’t worry, after reading this article you’ll know how to easily create one yourself. You can find the free example file at the top of this page.
Step 1: Creating a table with DAX
In Power BI you have the possibility to add a whole new table to the data model based on DAX. You can do this as follows:
- Go to the Data inspection-view (second tab on the left side of the screen).
- Go to the ‘Modeling’ tab.
- In the Ribbon choose the option ‘New Table’.
- In the formula bar you can then add a DAX formula that generates a table (see step 2).
Step 2: Entering the DAX formula calendar table
- Enter the following in the formula bar:
DimCalendar = CALENDARAUTO()
- Execute the formula by hitting Enter or clicking on the check mark next to the formula bar.
.The result is a table consisting of one column ‘Date’. This column contains the concatenated values composed of the MIN of the date fields and the MAX of the date fields that are available in the data model:.
- Change the Data Type to date-only so that the time indication is no longer visible:
In some cases you may have a technical date field with a value of 1-1-1900 that makes the table unnecessarily large. In this case it is best to determine the MIN and MAX yourself by selecting a specific date field or by manually entering a start and/or end date. If you want to determine the minimum and maximum date range yourself, you can use the CALENDAR function. This works as follows:
For example, you can then refer to the minimum and maximum date of a date field in a certain table. Example:.
DimCalendar Min OrderDate Max DueDate =
MIN ( FactInternetSales[OrderDate] ),
MAX ( FactInternetSales[OrderDate] )
Make sure that the selected table contains the MIN and MAX that at least appear in the other tables with date. Otherwise you don’t have a calendar table that has a too small date range. If you want to choose a date range yourself, you can use the following formula:
DimCalendar Range 2010 –> 2025 =
CALENDAR ( DATE ( 2010, 1, 1 ), DATE ( 2025, 1, 1 ) )
Make sure that you take a range that is sufficiently wide to include dates from future years in the calendar table. You can also choose to make the end date dynamic based on the current day plus an interval (for example today + 2 years):
DimCalendar Range 2010 –> Today + 2 Years =
CALENDAR ( DATE ( 2010, 1, 1 ), NOW () + 730 ).
Step 3: Adding data fields
There are two ways to add columns that are a property of the date field.
Method 1: Add a new calculated column:
- Click on “New Column”:
- .An example of a new date feature is ‘Year’. Enter the following formula in the formula bar:
Year =YEAR ( DimCalendar[Date] )
The result is a new column with only the year:.
Method 2: Adjusting the table formula: Using the ADDCOLUMNS function.
We are going to adjust this formula so that we can create and adjust the entire table in one formula. In terms of management, this is preferable because we then have to make adjustments in one place. We can also easily copy the formula to another report so that we immediately have an extensive calendar table..
We apply the following DAX function:
ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…)
This function returns a table definition <table> that can be expanded with new columns (<name>) and column content or formula (<expression>). You can add multiple columns by always filling in the pair “Column name”, <EXPRESSION>. Create a new DAX table. The formula for our calendar table where we add the column Year directly in the same formula will then look like this:.
You can then extend this calendar table all by yourself with all the data properties you need..
Below an extensive formula with the most common definitions:
DimCalendar AddColumns – Final Result =
“DateAsInteger”, FORMAT ( [Date], “YYYYMMDD” ),
“Year”, YEAR ( [Date] ),
“Monthnumber”, FORMAT ( [Date], “MM” ),
“YearMonthnumber”, FORMAT ( [Date], “YYYY/MM” ),
“YearMonthShort”, FORMAT ( [Date], “YYYY/mmm” ),
“MonthNameShort”, FORMAT ( [Date], “mmm” ),
“MonthNameLong”, FORMAT ( [Date], “mmmm” ),
“Weeknumber”, WEEKNUM ( [DATE] ),
“DayOfWeekNumber”, WEEKDAY ( [Date] ),
“DayOfWeek”, FORMAT ( [Date], “dddd” ),
“DayOfWeekShort”, FORMAT ( [Date], “ddd” ),
“Quarter”, “Q” & FORMAT ( [Date], “Q” ),
“YearQuarter”, FORMAT ( [Date], “YYYY” ) & “/Q”
& FORMAT ( [Date], “Q” )
If you work with ISO-Year / Weeks you can add these definitions:
- Iso Week: WEEKNUM([Date],21)
- Iso Year: IF( AND(WEEKNUM([Date],21) < 5,WEEKNUM([Date],2) > 50),[Year]+1,IF(AND(WEEKNUM([Date],21) > 50,WEEKNUM([Date],2) < 5),[Year]-1,[Year]))
- Iso Year Week: = IF( AND(WEEKNUM([Date],21) < 5,WEEKNUM([Date],2) > 50),[Year]+1,IF(AND(WEEKNUM([Date],21) > 50,WEEKNUM([Date],2) < 5),[Year]-1,[Year])) & ” week ” & FORMAT(WEEKNUM([Date],21),“00”)
Step 4: Mark table as date table
For the sake of performance optimization and working with time intelligence formulas, it is advisable to mark the Calendar Table as the date table. You can do this by clicking on “Mark as data table” under Modeling. Then choose the column “Date” as validation column:.
Step 5: Determining the sorting order of Month Names on the basis of Monthly Number Column
By default, the sorting of the values in Month Name columns is alphabetical. As a result, in a graph or table, “April” is always shown as the first month while you want to show the month of January first. January is month 1 of the year. In Power BI, you have the option “Sort By Column”. Proceed as follows:
- Select the column “MonthNameShort”. Under Modeling, click on “Sort By Column” and choose “Monthnumber”. From this moment on, the sorting order is applied on the basis of the month numbers. Do the same for the column “MonthNameLong”:.
Step 6: Establish links between the date calendar and transaction tables
Use the ‘Date’ column as a key field in the property table and the date fields in the transaction tables.
If a transaction table has ‘year’ as lowest granularity you can choose to create a Many-to-Many relationship by linking the column ‘Year’ from the Dimension table to ‘Year’ in a transaction table.
What do you think?
Which method do you prefer to add a date table in your data model? Put your comments below..