Time Intelligence: Calendar Table

[vivran22]

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 Year-over-Year variation, 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:

Calendar Table =
VAR StartYear =
    YEAR ( MIN ( factTable[DateColumn] ) ) //Calculate the start year and 
VAR EndYear =
    YEAR ( MAX ( factTable[DateColumn] ) ) // end year from a date column
RETURN
    ADDCOLUMNS (
        // Add columns in the calendar table
        CALENDAR (
            // Creating Calendar [Date] column
            DATE ( StartYear, 1, 1 ),
            // Start Date
            DATE ( EndYear, 12, 31 )
        ),
        "Year", YEAR ( [Date] ),
        // Adding column for Year
        "Quarter", "Q" & INT ( FORMAT ( [Date], "q" ) ),
        // Adding column for quarter
        "Month Year", FORMAT ( [Date], "mmm" ) & " "
            & YEAR ( [Date] )
    )

Once the calendar table is created, 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.