Learn how to Implement Three Use Circumstances for the New Calendar-Based mostly Time Intelligence

Introduction

about Time Intelligence in DAX prior to now.

Nonetheless, the brand new Calendar-based Time Intelligence function rewrites the rulebook, as some ideas will change, and the required methods might be less complicated than earlier than.

Anyway, the time intelligence features stay the identical, despite the fact that we should use them barely in another way. However nearly all the things might be simpler than earlier than.

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

Now, let’s focus on the use instances.

Use-Circumstances

Monetary calendar

When working with monetary information, it might 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 attainable 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 yr.
2. We want 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 attainable to calculate the earlier yr based mostly on weeks.

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

Weekly Calculation with Fiscal Years

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

Even such a state of affairs can now be solved with commonplace time intelligence features when the calendar desk incorporates the right data.

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 vital 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’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 intervals, like yr, 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 note 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 begin with, I create a desk containing the next data:

• 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 with two examples per column:

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

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

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

You will discover an instance of mix tables with lists of numbers on 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 have to 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 word 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 info to them:

When establishing 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 yr. The class “Month” should comprise the month and the yr, 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 under 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 state of affairs.

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 move 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 may see, the PY is calculated appropriately for the extra months.

As well as, in the best desk, you may see the values from December and the extra months (from the left desk) added collectively into December by utilizing 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 may see, the [YearOfWeek] column is tied to the week, not the calendar yr. I did this to make sure the right task 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 yr 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' )
                                )

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

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

Listed here 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 12 months begins on the primary day of August.

Which means 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 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 have to create a separate desk with the wanted columns. For some cause, 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 right 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 yr 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 yr, despite the fact that the dates are shifted.

That is precisely what I anticipated.

Conclusion

This new function modifications all the things concerning 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 right content material.

It’s fascinating that Microsoft already recommends utilizing this function, despite the fact that it’s solely been obtainable for just a few months since its introduction as a Preview function.

My advice is to look into it. Learn the articles linked under. Take a look at it along with your particular situations and determine 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 could improve the variety of date tables within the information mannequin. Till now, I’ve used a single central date desk for all the things. Now, I could have to create separate date tables for particular situations. However this could introduce complexity when combining completely different features of the info mannequin. This may introduce further challenges in deciphering the info.

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 customers?

I’ll completely keep away from such situations. 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 fascinating situations.

References

Right here, the Microsoft documentation for calendar-based time intelligence: Implement time-based calculations in Energy BI – Energy BI | Microsoft Study.

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 take advantage of the Contoso pattern dataset. You may obtain the ContosoRetailDW Dataset free of charge from Microsoft right here.

The Contoso Information can be utilized freely below the MIT License, as described on this doc. I modified the dataset to shift the info to up to date dates.