top of page
Search
2 min read

Power BI Time Intelligence - Calendar Table



Time Intelligence is a neat feature in Power BI. And if we understand a few necessary aspects of how it works, then designing a robust data model becomes a straightforward task. For a beginner, it becomes elementary that we all understand what it takes to build a robust data model with Time Intelligence.

A few frequently used Time-based calculations are the Year-over-Year trend, comparing performance from the previous month or previous year the same month, YTD, QTD, and MTD. Time Intelligence functions make this task relatively simple.

Experts strongly recommend the use of a dedicated calendar table in a data model. And if the calendar table is dynamic, then it has added advantages.


We can use the following DAX functions for creating a calendar table: CALENDARAUTO & CALENDAR. These functions return a table of one column of the DateTime data type.


CALENDARAUTO() – It scans all the date columns present in the entire data model, finds the minimum and maximum year referenced, and generates the set of dates between these years.


CALENDAR(Start Date, End Date)- It requires the upper and lower boundaries to generate the set of dates between these dates. The following code generates a simple calendar table called Date, containing all the dates between January 1, 2019, and December 31, 2020.

 

 Date = 
CALENDAR(
  DATE( 2019,1,1 ),
  DATE( 2020,12,31 )
)

You may include more columns in your calendar table:



ftCalendar = 
 ADDCOLUMNS(
  CALENDARAUTO(),
  "Year", YEAR([Date]),
  "Month", EOMONTH([Date],-1)+1,
  "MonthNum",MONTH([Date]),
  "MonthName", Format([Date],"mmm"),
  "Qtr", "Q" & FORMAT([Date],"q"),
  "YearQtr", YEAR([Date]) & " Q" & FORMAT([Date],"q"),
 
 //Calculating Financial year ending on March 31
 "FY Year", IF(MONTH([Date])>3,"FY "&YEAR([Date])&"- 
       "&RIGHT(YEAR([Date])+1,2),"FY "&YEAR([Date])-1&"- 
          "&RIGHT(YEAR([Date]),2)),
 
 //Calculating Financial Qtr
 "FY Qtr", "Q"&CEILING(MONTH(EOMONTH([Date],-3)),3)/3
 )

Post creation of the calendar table, one should:

· Mark it as a Date table






· Establish One-to-Many relationship (filter direction from calendar table to fact tables)





Points to consider while creating the calendar table:

  • It should contain all the days for all the years present in the data model. If the fact table contains data for the duration of May 10, 2018, to April 28, 2019, the range of dates in the calendar table is between January 1, 2018, and December 31, 2019.

  • One column should contain one column of DateTime or Date type, with unique values.

  • Mark the calendar as a Date table. Though it is not mandatory, it helps in writing correct DAX.

Benefits:

  • By using a calendar table, the model becomes more convenient to browse. Grouping various tables at the year, quarter, or month level becomes an easy task.

  • Most of the time intelligence functions in DAX require a separate calendar table.

NOTE: Power BI Desktop has a feature called “Auto Date/Time” (can be accessed through Options & Settings > Options> Data Load).


When enabled, Power BI automatically creates a calendar table for each Date or DateTime column in the model. These automatic calendar tables have two significant drawbacks:

  • Power BI generates one table per date column, which may result in inefficient data models.

  • The tables are hidden and with no modification capabilities. If one needs to add a column for the week, they cannot.

Hence, it is strongly recommended to disable the Auto Date/Time option to improve the efficiency of the data model:


From all the new Power BI desktop files:


From the current Power BI desktop file:




Comments

bottom of page