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

Introduction

about Time Intelligence in DAX previously.

Nonetheless, the brand new Calendar-based Time Intelligence characteristic rewrites the rulebook, as some ideas will change, and the required strategies will likely be easier than earlier than.

Anyway, the time intelligence features stay the identical, although we should use them barely otherwise. However nearly all the pieces will likely be simpler than earlier than.

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

Now, let’s focus on the use circumstances.

Use-Circumstances

Monetary calendar

When working with monetary knowledge, it might be essential to account for greater than 12 months to handle additional bookings. Some firms used such additional months to make sure consistency with the common bookings.

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

Now we are able to 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 knowledge; 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 potential to calculate the earlier 12 months primarily based on weeks.

Once more, this may now be carried out simply with a calendar that accommodates week info, together with the brand new TOTALWTD() and DATESWTD() features.

Weekly Calculation with Fiscal Years

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

Even such a situation can now be solved with normal time intelligence features when the calendar desk accommodates the right info.

Stipulations

The primary prerequisite is to allow the Preview characteristic:

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

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

When working with the brand new Time Intelligence potentialities, we’d like 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 durations, 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 necessary, because the desk have to be fastidiously crafted to cowl the required durations.

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

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

Case 1: Monetary Calendar

Initially, 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 Extra 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 Extra months are mapped to December

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

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

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

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

You will discover an instance of tips on how to 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 are able to 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 accommodates non-existent “dates”, equivalent to 30. February.

I click on on “Add Class” 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, keep in mind to click on on the “Validate knowledge” button after including every class. This helps you to find errors within the knowledge, 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 1 12 months. The class “Month” should comprise the month and the 12 months, whereas the “Month of the Yr” 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 totally different codecs and languages.

Please examine the hyperlinks within the References part beneath for detailed details about how this characteristic 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 a substitute of utilizing a Date column, I cross the identify of the Monetary calendar:

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

When trying on the outcomes for the intercalary year, I get this:

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

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

As well as, in the precise desk, you possibly can 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 take a look at weekly calculations.

Case 2: Weekly Calculation

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

As I already confirmed tips on how to 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 possibly can see, the [YearOfWeek] column is tied to the week, not the calendar 12 months. I did this to make sure the right task of the week to the Yr. 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 totally different years.

This reveals how necessary 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 characteristic 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 nearly 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 Yr 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 should have an extra column for the Fiscal Yr assigned to the weeks.

However this time, I have to create a separate desk with the wanted columns. For some motive, 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 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 consequence aligns completely with the week and weekday of the earlier 12 months, although the dates are shifted.

That is precisely what I anticipated.

Conclusion

This new characteristic adjustments all the pieces concerning Time Intelligence with DAX.

However, though we are able to simplify our DAX measures, we should take additional care when crafting our date tables. It’s all about having the right content material.

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

My advice is to look into it. Learn the articles linked beneath. Check it together with your particular eventualities and resolve whether or not it’s price switching current options to this characteristic.

I completely will use this characteristic when beginning new options.

The one disadvantage is that it could enhance the variety of date tables within the knowledge mannequin. Till now, I’ve used a single central date desk for all the pieces. Now, I could must create separate date tables for particular eventualities. However this may introduce complexity when combining totally different facets of the information mannequin. This may introduce extra challenges in deciphering the information.

Consider it for one second:

Is it actually a good suggestion to have two totally 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 subject. Will write extra about it over time after 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 characteristic 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 free of charge from Microsoft here.

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