Learn how to Implement Three Use Cases for the Recent Calendar-Based Time Intelligence

Introduction

about Time Intelligence in DAX up to now.

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

Anyway, the time intelligence functions remain the identical, although we must use them barely in a different way. But almost every thing will likely be easier than before.

On this piece, I’ll discuss three use cases for which I’d have needed to jot down complex DAX code.

Now, let’s discuss the use cases.

Use-Cases

Financial calendar

When working with financial data, it might be obligatory to account for greater than 12 months to administer extra bookings. Some firms used such extra months to make sure consistency with the regular bookings.

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

Now we are able to create a custom calendar and use the usual time intelligence functions with it.

This use case has the next requirements:

1. We must cover 15 months per yr.
2. We want two views: One for the booking months and one where the extra months are mapped to December.
3. We’re 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 yr based on weeks.

Again, this will now be done easily with a calendar that comprises week information, together with the brand new TOTALWTD() and DATESWTD() functions.

Weekly Calculation with Fiscal Years

Weekly Calculations are much more complicated once we must account for fiscal years that don’t match calendar years.

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

Prerequisites

The primary prerequisite is to enable the Preview feature:

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

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

When working with the brand new Time Intelligence possibilities, we’d like three steps:

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

The primary two steps are equally vital, because the table should be fastidiously crafted to cover the required periods.

The calendar definition allows us to make use of the columns within the date table to assign them to predefined categories. Shortly, you will notice what this implies.

I’ll describe the content of the date table with sample data for every use case.

Case 1: Financial Calendar

To start with, I create a table containing the next information:

• Years
• Semesters (1 & 2 plus a 3rd for the extra months)
• Quarters (1 – 4 and a fifth Quarter for the extra months) 
• Month 1 – 15
• Month Names for all 15 months (Jan – Dec, and Additional Month 1 – 3)
• 31 Days for every 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

That is the way it looks with two examples per column:

The 2 examples are one for January and one for the primary additional month.

Here, a special view of the information for the extra columns and rows:

In my case, I’ve built this table in SQL, but it may well be done in every other programming language, including Power Query.
You simply need multiple lists of numbers and mix them.

You will discover an example of how one can mix tables with lists of numbers in this text.

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

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

Now, I’ll create two recent calendars.

Please note that I cannot set the brand new table as a date table, because it comprises non-existent “dates”, comparable to 30. February.

I click on “Add Category” so as to add, for instance, the Yr, Month, and Month of Yr, and assign the columns with the information to them:

When organising the calendar, remember to click on the “Validate data” button after adding each category. This helps you to find errors in the information, in the event that they exist.

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

For instance, every month must belong to at least one yr. The category “Month” must contain the month and the yr, while the “Month of the Yr” must contain only the month.

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

Please check the links within the References section below for detailed details about how this feature works.

To have a calendar, where the extra months are assigned to December, I arrange the next Calendar:

I didn’t arrange columns for the Quarter within 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 as a substitute 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 the outcomes for the bissextile year, I get this:

And when the outcomes for the extra months, I get this:

As you may see, the PY is calculated accurately for the extra months.

As well as, in the fitting table, you may see the values from December and the extra months (from the left table) added together into December by utilizing the “Real” calendar.

To have such an answer with an easy measure is groundbreaking.

Now, let’s have a look at weekly calculations.

Case 2: Weekly Calculation

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

As I already showed how one can define custom calendars, I’ll only show you the content of the involved columns and the way they’re assigned to the calendar.

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

As you may see, the [YearOfWeek] column is tied to the week, not the calendar yr. I did this to make sure the right project of the week to the Yr. Without this, the Calendar Validation would have failed, because the [WeekKey] column for the primary calendar week of every year would have been assigned to 2 different years.

This shows how vital it’s to construct a consistent calendar table.

Here is the definition of the weekly calendar:

And listed below are the outcomes for the measure using this calendar:

As before, the measure uses an easy 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 recent feature to calculate a consistent weekly-based PY measure.

Listed here are the outcomes of a measure using the brand new WTD measures:

Listed 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’s to create these measures.

Case 3: Weekly Calculation with Fiscal Years

This one is more complex.

On this case, the Fiscal Yr starts on the primary day of August.

Which means that the primary week of the Fiscal yr is the week with the Fiscal yr’s first day.

I arrange all of the columns within the Date table; that is an extract of the needed columns:

Again, I will need to have an extra column for the Fiscal Yr assigned to the weeks.

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

You may read more about this here.

Ultimately, 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 yr is, again, straightforward:

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

These are the outcomes:

You may see that the result aligns perfectly with the week and weekday of the previous yr, although the dates are shifted.

This is strictly what I expected.

Conclusion

This recent feature changes every thing regarding Time Intelligence with DAX.

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

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

My suggestion is to look into it. Read the articles linked below. Test it together with your specific scenarios and choose whether it’s price switching existing solutions to this feature.

I absolutely will use this feature when starting recent solutions.

The one drawback is that it may well increase the variety of date tables in the information model. Until now, I actually have used a single central date table for every thing. Now, I might have to create separate date tables for specific scenarios. But this will introduce complexity when combining different points of the information model. This may introduce additional challenges in interpreting the information.

Consider it for one moment:

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

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

Stay tuned for more content on this topic. Will write more about it over time once 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 recent feature in great detail: Introducing Calendar-based Time intelligence in DAX – SQLBI.

Like in my previous articles, I exploit the Contoso sample dataset. You may download the ContosoRetailDW Dataset free of charge from Microsoft here.

The Contoso Data might be used freely under the MIT License, as described on this document. I modified the dataset to shift the information to contemporary dates.