How to Implement Three Use Cases for the New Calendar-Based Time Intelligence

Introduction

about Time Intelligence in DAX in the past.

However, the new Calendar-based Time Intelligence feature rewrites the rulebook, as some concepts will change, and the required techniques will be simpler than before.

Anyway, the time intelligence functions remain the same, even though we must use them slightly differently. But almost everything will be easier than before.

In this piece, I will discuss three use cases for which I would have needed to write complex DAX code.

Now, let’s discuss the use cases.

Use-Cases

Financial calendar

When working with financial data, it may be necessary to account for more than 12 months to manage extra bookings. Some companies used such extra months to ensure consistency with the regular bookings.

Until now, this has only been possible with custom DAX code.

Now we can create a custom calendar and use the standard time intelligence functions with it.

This use case has the following requirements:

1. We must cover 15 months per year.
2. We need two views: One for the booking months and one where the additional months are mapped to December.
3. We are looking at PY data; we must see all days, including leap years (29^th of February).

Weekly calculations

I already wrote about weekly calculations. It involved custom DAX code, as there was no WDT function, and it wasn’t possible to calculate the previous year based on weeks.

Again, this can now be done easily with a calendar that contains week information, along with the new TOTALWTD() and DATESWTD() functions.

Weekly Calculation with Fiscal Years

Weekly Calculations are even more complicated when we must account for fiscal years that do not match calendar years.

Even such a scenario can now be solved with standard time intelligence functions when the calendar table contains the correct information.

Prerequisites

The first prerequisite is to enable the Preview feature:

Another prerequisite is to create a date table that covers the required periods.

As before, a well-crafted date table is key for working with Time Intelligence, and now even more so with the new feature.

When working with the new Time Intelligence possibilities, we need three steps:

1. We build a date table and define which columns we need to cover each use case.
2. Then we define a calendar for this table by assigning the columns to the periods, like year, quarter, months, weeks, and dates.
3. Create DAX measures using the calendar defined in step 2.

The first two steps are equally important, as the table must be carefully crafted to cover the required periods.

The calendar definition allows us to use the columns in the date table to assign them to predefined categories. Shortly, you will see what this means.

I will describe the content of the date table with sample data for each use case.

Case 1: Financial Calendar

First of all, I create a table containing the following information:

• Years
• Semesters (1 & 2 plus a third for the additional months)
• Quarters (1 – 4 and a fifth Quarter for the additional months) 
• Month 1 – 15
• Month Names for all 15 months (Jan – Dec, and Additional Month 1 – 3)
• 31 Days for each month, even for the months that normally have fewer than 31 days
• The Quarter and the Months where the Additional months are mapped to December

This is how it looks with two examples per column:

The two examples are one for January and one for the first additional month.

Here, a different view of the data for the additional columns and rows:

In my case, I’ve built this table in SQL, but it can be done in any other programming language, including Power Query.
You only need multiple lists of numbers and combine them.

You can find an example of how to combine tables with lists of numbers in this article.

But the key point is that I am free to define the Calendar’s content. Even the Date column doesn’t need to contain real dates, as, in my case, there are only strings in it.

After importing the new calendar into Power BI, we can open the new “Calendar options” dialog after clicking on the table:

Now, I will create two new calendars.

Please note that I cannot set the new table as a date table, as it contains non-existent “dates”, such as 30. February.

I click on “Add Category” to add, for example, the Year, Month, and Month of Year, and assign the columns with the data to them:

When setting up the calendar, remember to click on the “Validate data” button after adding each category. This helps you in finding errors in the data, if they exist.

This button checks whether each value has a many-to-one relationship to each value in the category above.

For example, each month must belong to one year. The category “Month” must contain the month and the year, while the “Month of the Year” must contain only the month.

As the primary column, I selected the ID columns, and for the Associated columns, I selected the naming columns with different formats and languages.

Please check the links in the References section below for detailed information about how this feature works.

To have a calendar, where the additional months are assigned to December, I set up the following Calendar:

I didn’t set up columns for the Quarter in the “Real” calendar because I don’t need them in my scenario.

To calculate the PY for Online Sales, I can use the SAMEPERIODLASTYEAR() function. But instead of using a Date column, I pass the name of the Financial calendar:

Online Sales Fin PY = 
CALCULATE([Sum Online Sales],
            SAMEPERIODLASTYEAR('Financial_Calendar')
            )

When looking at the results for the leap year, I get this:

And when looking at the results for the additional months, I get this:

As you can see, the PY is calculated correctly for the additional months.

In addition, in the right table, you can see the values from December and the additional months (from the left table) added together into December by using the “Real” calendar.

To have such a solution with a simple measure is groundbreaking.

Now, let’s look at weekly calculations.

Case 2: Weekly Calculation

This time, I want to calculate the PY per week.

As I already showed how to define custom calendars, I will only show you the content of the involved columns and how they are assigned to the calendar.

This time, I used existing columns from my date table:

As you can see, the [YearOfWeek] column is tied to the week, not the calendar year. I did this to ensure the correct assignment of the week to the Year. Without this, the Calendar Validation would have failed, as the [WeekKey] column for the first calendar week of each year would have been assigned to two different years.

This shows how important it is to build a consistent calendar table.

Here is the definition of the weekly calendar:

And here are the results for the measure using this calendar:

As before, the measure uses a simple SAMEPRIODLASTYEAR() call and uses the newly created “Weekly Calendar”:

Online Sales PY Week = CALCULATE([Sum Online Sales]
                                ,SAMEPERIODLASTYEAR( 'Weekly Calendar' )
                                )

Compare this to the complex code you had before introducing this new feature to calculate a consistent weekly-based PY measure.

Here are the results of a measure using the new WTD measures:

Here are the measures used:

Online Sales WTD = 
    VAR WtdDates = DATESWTD('Weekly Calendar')

RETURN
    CALCULATE([Sum Online Sales]
                ,WtdDates
                )

Online Sales PY WTD = 
    CALCULATE([Online Sales WTD]
                ,SAMEPERIODLASTYEAR('Weekly Calendar')
                )

It’s almost unbelievable how easy it is to create these measures.

Case 3: Weekly Calculation with Fiscal Years

This one is more complex.

In this case, the Fiscal Year starts on the first day of August.

This means that the first week of the Fiscal year is the week with the Fiscal year’s first day.

I set up all the columns in the Date table; this is an extract of the needed columns:

Again, I must have an additional column for the Fiscal Year assigned to the weeks.

But this time, I must create a separate table with the needed columns. For some reason, using these columns from the Date table doesn’t work. Any attempt to use these columns resulted in a weird effect.

You can read more about this here.

In the end, I added a calculated table with the needed columns:

Fiscal-Week Date = 
CALCULATETABLE(
		SUMMARIZECOLUMNS(
				'Date'[FiscalYearForWeek]
				,'Date'[Fiscal Week/Year]
				,'Date'[FiscalWeekSort]
				,'Date'[Day of Week Name]
				,'Date'[Day of Week]
				,'Date'[Date]
				,'Date'[DateKey])
            ,NOT ISBLANK('Date'[FiscalYearForWeek] )
            )

The calendar created on this table looks like this:

The measure to calculate the Sales for the previous year is, again, straightforward:

Online Sales PY (Fiscal Week) = 
    CALCULATE([Sum Online Sales]
                ,SAMEPERIODLASTYEAR('Fiscal-Week Calendar')
                )

These are the results:

You can see that the result aligns perfectly with the week and weekday of the previous year, even though the dates are shifted.

This is exactly what I expected.

Conclusion

This new feature changes everything regarding Time Intelligence with DAX.

But, although we can simplify our DAX measures, we must take extra care when crafting our date tables. It’s all about having the correct content.

It’s interesting that Microsoft already recommends using this feature, even though it’s only been available for a few months since its introduction as a Preview feature.

My recommendation is to look into it. Read the articles linked below. Test it with your specific scenarios and decide whether it’s worth switching existing solutions to this feature.

I absolutely will use this feature when starting new solutions.

The only drawback is that it can increase the number of date tables in the data model. Until now, I have used a single central date table for everything. Now, I may need to create separate date tables for specific scenarios. But this can introduce complexity when combining different aspects of the data model. This would introduce additional challenges in interpreting the data.

Think of it for one moment:

Is it really a good idea to have two different calendars on one page? Are the results still comparable? Can this confuse your consumers?

I will absolutely avoid such scenarios. Comparing results by month and by week on the same pages, or even in the same report, makes little sense to me.

Stay tuned for more content on this topic. Will write more about it over time when I encounter interesting scenarios.

References

Here, the Microsoft documentation for calendar-based time intelligence: Implement time-based calculations in Power BI – Power BI | Microsoft Learn.

This SQL BI article explains this new feature in great detail: Introducing Calendar-based Time intelligence in DAX – SQLBI.

Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be used freely under the MIT License, as described in this document. I changed the dataset to shift the data to contemporary dates.