Time Intelligence in Data Modeling Part 2: Dynamic Calendar with DAX in Power Pivot and Power BI

Time Intelligence in Data Modelling Part 1: Calendar Table

A calendar table is an essential part of every data model. We have seen how it looks and feels in Part 1 of the series. Let’s look at how to quickly create a dynamic calendar with DAX in Power Pivot and Power BI.

Remove Time and Birth Date Columns

We will use the functions that automatically detect the earliest and latest dates in your data and generate a Calendar table. Since we don’t want to end up with a massive calendar table it’s important to spend some time assessing your data. Identify relevant and redundant date columns. Things like the birth dates of employees are usually meaningless for business analysis. If not necessary, you definitely don’t want them in your calendar table, as they could push the start date of your calendar decades before your actual business data starts.
For example, it the company runs from 2000 and one employe birth date is 9/1/1970, this would add up 30 years of unnecessary rows to your calendar. Similarly with time data. Each time data is read as time on day 1/0/1900, adding up an entire century of rows to your calendar table. Remove the time and birth date columns from your data.

Dynamic Calendar with DAX in Power Pivot

Creating a calendar table in Power Pivot is fairly simple. We remove time and birth date columns from our tables. In our example we have four tables that we loaded into Data Model.

We then select Design > Date Table > New.

We are now ready to create a calendar table. We select Table tools > New table.

We type in Calendar = CALENDARAUTO().

A new table Calendar is created, containing all the dates between the earliest and latest date in our data. We add the atributes with DAX formulas:

Year = YEAR(‘Calendar'[Date]),
Quarter = QUARTER(‘Calendar'[Date]),
Month = MONTH(‘Calendar'[Date]),
Year = YEAR(‘Calendar'[Date]),
WeekdayNo = WEEKDAY(Calendar[Date]),
WeekNo = WEEKNUM(Calendar[Date]), etc.

We can also create Calendar table with all the attributes using a convinient formula bellow:

Calendar = ADDCOLUMNS ( CALENDARAUTO (),
“Year”, FORMAT([Date], “yyyy”),
“MonthNo”, MONTH([Date]),
“Month”, FORMAT([Date],”MMM”),
“Quarter”, FORMAT([Date],”\QQ”),
“YearMonth”, FORMAT([Date],”YYYY-MM”),
“WeekdayNo”, WEEKDAY([Date],2), //1-Sun..Sat; 2-Mon..Sat
“Weekday”, FORMAT([Date],”ddd”),
“WeekNo”, WEEKNUM([Date], 2),
“Week”, “W” & WEEKNUM([Date], 2) )

You can watch a video describing this technique here:

We lastly connect the Calendar table with the fact tables: we drag Date and drop it on OrderDate in table Sales and Month in table Budget. We are now set to use our calendar.

Creating a table using CALENDARAUTO() is not the best way to go. Data refresh could bring in a new time column that will be treated as a date 1/0/1900 and size up our calendar considerebly. Using Power Query for creating a calendar table gives us more control. We will learn how to create calendar table in Power Query in the next part of the series.