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

Introduction

about Time Intelligence in DAX prior to now.

Nevertheless, the brand new Calendar-based Time Intelligence function rewrites the rulebook, as some ideas will change, and the required methods can be easier than earlier than.

Anyway, the time intelligence features stay the identical, though we should use them barely in another way. However virtually every thing can be simpler than earlier than.

On this piece, I’ll focus on three use circumstances for which I might have wanted to jot down complicated DAX code.

Now, let’s focus on the use circumstances.

Use-Instances

Monetary calendar

When working with monetary information, it could be essential to account for greater than 12 months to handle further bookings. Some corporations used such further months to make sure consistency with the common bookings.

Till now, this has solely been doable with customized DAX code.

Now we will create a customized calendar and use the usual time intelligence features with it.

This use case has the next necessities:

1. We should cowl 15 months per 12 months.
2. We’d like two views: One for the reserving months and one the place the extra months are mapped to December.
3. We’re PY information; we should see all days, together with leap years (29^th of February).

Weekly calculations

I already wrote about weekly calculations. It concerned customized DAX code, as there was no WDT operate, and it wasn’t doable to calculate the earlier 12 months based mostly on weeks.

Once more, this could now be finished simply with a calendar that incorporates week info, together with the brand new TOTALWTD() and DATESWTD() features.

Weekly Calculation with Fiscal Years

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

Even such a situation can now be solved with customary time intelligence features when the calendar desk incorporates the proper info.

Conditions

The primary prerequisite is to allow the Preview function:

One other prerequisite is to create a date desk that covers the required intervals.

As earlier than, a well-crafted date desk is essential for working with Time Intelligence, and now much more so with the brand new function.

When working with the brand new Time Intelligence prospects, we want three steps:

1. We construct a date desk and outline which columns we have to cowl every use case.
2. Then we outline a calendar for this desk by assigning the columns to the intervals, like 12 months, quarter, months, weeks, and dates.
3. Create DAX measures utilizing the calendar outlined in step 2.

The primary two steps are equally essential, because the desk have to be fastidiously crafted to cowl the required intervals.

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

I’ll describe the content material of the date desk with pattern information for every use case.

Case 1: Monetary Calendar

To start with, I create a desk containing the next info:

• 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 Further Month 1 – 3)
• 31 Days for every month, even for the months that usually have fewer than 31 days
• The Quarter and the Months the place the Further months are mapped to December

That is the way it appears to be like with two examples per column:

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

Right here, a distinct view of the information for the extra columns and rows:

In my case, I’ve constructed this desk in SQL, however it may be finished in every other programming language, together with Energy Question.
You solely want a number of lists of numbers and mix them.

You could find an instance of mix tables with lists of numbers in this article.

However the important thing level is that I’m free to outline the Calendar’s content material. Even the Date column doesn’t must comprise actual dates, as, in my case, there are solely strings in it.

After importing the brand new calendar into Energy BI, we will open the brand new “Calendar choices” dialog after clicking on the desk:

Now, I’ll create two new calendars.

Please be aware that I can’t set the brand new desk as a date desk, because it incorporates non-existent “dates”, akin to 30. February.

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

When organising the calendar, keep in mind to click on on the “Validate information” button after including every class. This helps you to find errors within the information, in the event that they exist.

This button checks whether or not every worth has a many-to-one relationship to every worth within the class above.

For instance, every month should belong to at least one 12 months. The class “Month” should comprise the month and the 12 months, whereas the “Month of the 12 months” should comprise solely the month.

As the first column, I chosen the ID columns, and for the Related columns, I chosen the naming columns with completely different codecs and languages.

Please examine the hyperlinks within the References part beneath for detailed details about how this function works.

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

I didn’t arrange columns for the Quarter within the “Actual” calendar as a result of I don’t want them in my situation.

To calculate the PY for On-line Gross sales, I can use the SAMEPERIODLASTYEAR() operate. However as an alternative of utilizing a Date column, I go the identify of the Monetary calendar:

On-line Gross sales Fin PY = 
CALCULATE([Sum Online Sales],
            SAMEPERIODLASTYEAR('Financial_Calendar')
            )

When wanting on the outcomes for the bissextile year, I get this:

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

As you’ll be able to see, the PY is calculated appropriately for the extra months.

As well as, in the precise desk, you’ll be able to see the values from December and the extra months (from the left desk) added collectively into December through the use of the “Actual” calendar.

To have such an answer with a 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 confirmed outline customized calendars, I’ll solely present you the content material of the concerned columns and the way they’re assigned to the calendar.

This time, I used current columns from my date desk:

As you’ll be able to see, the [YearOfWeek] column is tied to the week, not the calendar 12 months. I did this to make sure the proper project of the week to the 12 months. With out this, the Calendar Validation would have failed, because the [WeekKey] column for the primary calendar week of every 12 months would have been assigned to 2 completely different years.

This exhibits how essential it’s to construct a constant calendar desk.

Right here is the definition of the weekly calendar:

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

As earlier than, the measure makes use of a easy SAMEPRIODLASTYEAR() name and makes use of the newly created “Weekly Calendar”:

On-line Gross sales PY Week = CALCULATE([Sum Online Sales]
                                ,SAMEPERIODLASTYEAR( 'Weekly Calendar' )
                                )

Examine this to the complicated code you had earlier than introducing this new function to calculate a constant weekly-based PY measure.

Listed below are the outcomes of a measure utilizing the brand new WTD measures:

Listed below are the measures used:

On-line Gross sales WTD = 
    VAR WtdDates = DATESWTD('Weekly Calendar')

RETURN
    CALCULATE([Sum Online Sales]
                ,WtdDates
                )

On-line Gross sales PY WTD = 
    CALCULATE([Online Sales WTD]
                ,SAMEPERIODLASTYEAR('Weekly Calendar')
                )

It’s virtually unbelievable how simple it’s to create these measures.

Case 3: Weekly Calculation with Fiscal Years

This one is extra complicated.

On this case, the Fiscal 12 months begins on the primary day of August.

Because of this the primary week of the Fiscal 12 months is the week with the Fiscal 12 months’s first day.

I arrange all of the columns within the Date desk; that is an extract of the wanted columns:

Once more, I will need to have an extra column for the Fiscal 12 months assigned to the weeks.

However this time, I need to create a separate desk with the wanted columns. For some purpose, utilizing these columns from the Date desk doesn’t work. Any try to make use of these columns resulted in a bizarre impact.

You may learn extra about this here.

Ultimately, I added a calculated desk with the wanted 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 desk appears to be like like this:

The measure to calculate the Gross sales for the earlier 12 months is, once more, easy:

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

These are the outcomes:

You may see that the outcome aligns completely with the week and weekday of the earlier 12 months, though the dates are shifted.

That is precisely what I anticipated.

Conclusion

This new function modifications every thing relating to Time Intelligence with DAX.

However, though we will simplify our DAX measures, we should take further care when crafting our date tables. It’s all about having the proper content material.

It’s attention-grabbing that Microsoft already recommends utilizing this function, though it’s solely been accessible for a number of months since its introduction as a Preview function.

My suggestion is to look into it. Learn the articles linked beneath. Take a look at it along with your particular eventualities and resolve whether or not it’s value switching current options to this function.

I completely will use this function when beginning new options.

The one downside is that it may improve the variety of date tables within the information mannequin. Till now, I’ve used a single central date desk for every thing. Now, I’ll must create separate date tables for particular eventualities. However this could introduce complexity when combining completely different points of the information mannequin. This could introduce extra challenges in decoding the information.

Consider it for one second:

Is it actually a good suggestion to have two completely different calendars on one web page? Are the outcomes nonetheless comparable? Can this confuse your shoppers?

I’ll completely keep away from such eventualities. Evaluating outcomes by month and by week on the identical pages, and even in the identical report, makes little sense to me.

Keep tuned for extra content material on this matter. Will write extra about it over time once I encounter attention-grabbing eventualities.

References

Right 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 function in nice element: Introducing Calendar-based Time intelligence in DAX – SQLBI.

Like in my earlier articles, I exploit the Contoso pattern dataset. You may obtain the ContosoRetailDW Dataset totally free from Microsoft here.

The Contoso Knowledge can be utilized freely underneath the MIT License, as described in this document. I modified the dataset to shift the information to up to date dates.