When Issues Get Bizarre with Customized Calendars in Tabular Fashions

Introduction

After the preliminary euphoria with the brand new calendar-based time intelligence, I began trying deeper into the brand new function to see what these new potentialities imply in the true world.

You’ll find a number of hyperlinks about it within the References part on the finish of this piece, together with a SQLBI article, which takes you deep into the subject.

I strongly suggest studying these articles to realize a superb understanding.

However over time, I noticed there are darker sides to this shiny new function.

Now I’ll present you 4 examples, the place I found attention-grabbing results.

I’ll provide workarounds or options to every problem when potential.

Setup of the Calendars

For this piece, I used two Energy BI stories with two Date tables every to keep away from interference. All Date tables have the identical supply desk.

A potential interference between Calendars is described right here.

For the Gregorian calendar, I used this configuration:

For the Week-based calendar, I used this configuration:

The Weekly calendar contains the YearOfWeek column for the yr class.

This column accommodates the week-aligned yr, which is required for such a calendar. This column relies on the ISO-week definition. Every year begins on Monday of week 1.

Yow will discover an evidence for the ISO week right here.

Each Energy BI knowledge fashions used the identical configuration.

Earlier Months and completely different month lengths

OK, first, let’s take a look at months with completely different lengths.

I describe this case to make you conscious of the variations from the basic time-intelligence logic.

I created two measures:

On-line Gross sales (PM) =

    CALCULATE([Online Sales]

                ,DATEADD('Date'[Date], -1, MONTH)

                )

And this one makes use of the Gregorian calendar:

On-line Gross sales (PY Gregorian) =

    CALCULATE([Online Sales]

                ,DATEADD('Gregorian Calendar', -1, YEAR)

                )

I added each to a desk visible.

Now take a look at the variations between these two measures for March:

Whereas this end result could be very attention-grabbing, take a look at this one:

In each instances, the end result could be very completely different.

Whereas the measure utilizing basic time intelligence exhibits the identical worth for the final three days of March, the outcomes for February omit the final days of January.

The Calendar-based measure performs significantly better.

The essential level right here is that the row sums equal the sum proven within the Complete row.

Furthermore, the DATEADD() perform now has two further parameters that have an effect on outcomes for months with unequal lengths.

Whereas it’s not bizarre, it’s positively a unique habits of the perform, which you have to concentrate on. This is applicable in all places when durations aren’t of the identical size. I’ll come again to this later.

What occurs with the earlier yr?

Now comes the primary bizarre state of affairs.

Observe the next desk utilizing a measure with a DATEADD() name utilizing the Gregorian calendar for PY:

As you may see, every thing appears nice.

Now take a look at the outcomes, when evaluating 2024 to 2025:

As you may see, the PY values for March 2025 are shifted by 1 day.

This isn’t appropriate.

Even worse, when evaluating the months’ whole values, they’re equal between 2024 and the PY measure in 2025.

This impact is observable as much as December, the place the outcomes are these:

This is similar impact we are able to observe within the Earlier month measure proven earlier, since these two years aren’t the identical size.

This bizarre impact is because of how DAX calculates outcomes based mostly on the calendar hierarchy.

The mechanism known as “Distance from Parent”.

However the Dad or mum is outlined by the third parameter of DATEADD(): 12 months

Subsequently, DATEADD() calculates the space from the start of the yr and returns the end result utilizing the identical distance for the earlier yr.

One answer to this problem is to make sure that all months are of equal size.

In my first article about this new function, linked within the References part under, I created a customized date desk and a calendar with 31 days for all months.

When performing the identical operation with that calendar, the impact disappears:

Whereas this strategy works flawlessly, it requires a customized calendar, which might trigger different points or fail to cowl particular necessities. Particularly because the date columns don’t include actual dates, and the date_real column has gaps. This could trigger points when utilizing it in customized calculations.

One other answer is to calculate the PY by transferring again by 12 months:

On-line Gross sales (-12 M Gregorian) =

    CALCULATE([Online Sales]

                ,DATEADD('Gregorian Calendar', -12, MONTH)

                )

And these are the outcomes of the brand new measure:

In crimson, you see the identical outcomes as earlier than, shifted by in the future.

In inexperienced, you see the outcomes for the measure with month granularity.

Apparently, the sums for the quarters and the years are appropriate as nicely.

In the intervening time, I don’t see any problem with utilizing this strategy, and I’ll use and check it sooner or later.

Weekly calculations – Head scratching

It is a very unusual one.

Have a look at the next image with the identical desk in several states side-by-side:

On the left, you see that every one rows for 2023 are equivalent when 2022 is collapsed.

On the suitable, you see the proper values for 2023, however they’re displayed solely once I broaden at the very least one week of 2022 as much as the Date.

However the values in 2022 are once more all the identical.

I skilled this already and confirmed this in my first article concerning the calendar function (Hyperlink under).

In that occasion, I solved it by making a separate desk for the weekly calendar. However this time it didn’t work.

I needed to rebuild the info mannequin from scratch, and it labored instantly:

As you may see, the outcomes are appropriate.

In case you look fastidiously, the PY outcomes are appropriate to get the PY worth of the identical week and weekday of the earlier yr.

I’ve no clue what the distinction is between these two setups.

The Date desk is from the identical supply in each knowledge fashions, and the calendar is outlined by utilizing the identical columns.

However I’m a little bit anxious about this as a result of I don’t perceive the rationale and don’t have an answer. Even after reviewing the TMDL file for that desk, I didn’t discover something that pointed to the trigger.

I encountered such an impact solely with weekly calculations.

Mixing weekly with month-to-month logic

Certainly one of my purchasers desires to see a report displaying the day by day outcomes for the present month, in contrast with the identical week and weekday of the earlier yr.

It is a mixture of the month-to-month (Gregorian) Calendar with the weekly logic.

As I’ll present within the subsequent case in additional element, the weekly logic appropriately maps the weeks and weekdays to the earlier yr. Subsequently, this needs to be an issue.

However because the weeks don’t align with the months, I can not add the Month class. I’ll get an error when validating when attempting so as to add the Month class.

Subsequently, I can not use an MTD calculation, because the perform is not going to discover the wanted class:

I can not add a Gregorian calendar to the identical date desk, because the engine expects the identical column for a similar Class for all Calendars on the identical desk.

See right here for Microsoft’s assertion about this.

Since I exploit the YearForWeek column for the 12 months class, it is not going to work with the Month class as a result of they don’t align.

As a consequence, I needed to write customized logic to resolve all the necessities.

Weekly calculations – That’s attention-grabbing!

To finish on a optimistic observe, I can present you one thing that works very nicely.

Keep in mind the difficulty with the months that aren’t of the identical size and the way the PY values have been shifted?

This impact doesn’t seem when performing weekly calculations.

As you may see, the outcomes are appropriately calculated based mostly on the week and the proper weekdays.

As anticipated, the values aren’t mapped to the dates of the earlier yr however to the weekdays per week.

That is what I anticipate when observing outcomes by week and weekdays.

The reason being that every week is similar size, and the date desk is constructed to assist such a situation.

Conclusion

As you may see, the outcomes are combined.

When trying on the outcomes from earlier durations of various lengths (months or years), the outcomes shift.

When the durations are of the identical size (weeks or the customized calendar), then every thing works as anticipated.

I used to be extraordinarily shocked and upset once I noticed the outcomes for the leap years.

However luckily, this may be solved by understanding how the brand new logic works.

The opposite problem with which I’ve a nasty feeling is the inconsistent functioning of the weekly based mostly calendar and the PY calculation.

That is disturbing, because it’s not at all times that simple to rebuild an information mannequin.

One other problem I’ve is that SQLBI stories potential points when utilizing a number of calendars in the identical date desk of their article. I’ve added a hyperlink to it under.

This may introduce the necessity for a number of date tables in the identical knowledge mannequin.

One thing I’m reluctant to do.

I can think about this impacts a number of visuals in a report, the place they use the logic of various calendars however with completely different classes.

This may be difficult to resolve.

However we’ll see how this function will evolve, as we’re nonetheless in Preview.

References

The SQLBI article explaining the Calendar-based time intelligence function intimately:

The SQLBI article explaining DATEADD() with the brand new parameters:

Microsoft’s documentation on the brand new function (URL may change over time):

My article with three real-world use instances with the brand new calendars: