topic Interesting effects with calendar-based time intelligence in DAX Commands and Tips

Interesting effects with calendar-based time intelligence

SalvaC — Mon, 13 Apr 2026 05:46:41 GMT

Hi guys

After some research and testing, I found several interesting effects with the new calendar-based time intelligence.

For example, how dates are shifted when calculating the PY with leap years, or some strange effects with weekly calculations.

I summarized them here for everyone to read:

https://towardsdatascience.com/when-things-get-weird-with-the-custom-calendars-in-tabular-models/

I hope that you find it interesting.

Regards,

Salvatore

Re: Interesting effects with calendar-based time intelligence

FBergamaschi — Mon, 13 Apr 2026 07:11:53 GMT

Hi SalvaC

thanks for the insight but this is a forum for support requests, just FYI

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Interesting effects with calendar-based time intelligence

SalvaC — Mon, 13 Apr 2026 07:15:32 GMT

Hi FBergamaschi

Thanks.

You are very welcome to give feedback or if you can provide an answer for the issues I wasn't able to find a solution.

Where would be the correct place to post this?

Kind regards

Salvatore

Re: Interesting effects with calendar-based time intelligence

FBergamaschi — Wed, 15 Apr 2026 10:45:59 GMT

Hi SalvaC

please send me the linkd to the questions you could not find an answer for, especially on DAX and I shall support.

I would consider a blog among the ones you have options to choose in this forum for the post on the new Time Intelligence

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Interesting effects with calendar-based time intelligence

SalvaC — Thu, 16 Apr 2026 05:32:49 GMT

Good morning FBergamaschi

Thank you for your offer.

The one issue I wans't able to solve is the following:

I have a datamodel with two date tables:

For the year-month-date calendar
The second for the year-week-date calendar

When I add a PY measure to a matrix based on the year-week-date calendar I get these results:

Observe how the results on the left side are wrong as long as the year 2022 is collapsed.

On the right side you see the (correct) results when 2022 is expanded. But only when at least one week is expanded to the date.

I then created a new data model from scratch based on the same data and rebuild the calendars and the same measures and I get this:

The correct results right from the start.

The calendars in the two data models are set up in the same way:

In both data models the PY measure is this:

Online Sales (PY Weekly) = CALCULATE([Online Sales] ,DATEADD('Weekly Calendar', -1, YEAR) )

I cannot understand why this happens it the first data model.

I removed everything and rebuild the calendar and the measures in the first data model, but it didn't help.

Has anybody an idea what happens here?

Regards,

Salvatore

P.S.: Anyway, I invite you to read my article linked in the first post here, as it might contain interesting information on the behavior of the new calendar-based time intelligence.

Re: Interesting effects with calendar-based time intelligence

FBergamaschi — Fri, 17 Apr 2026 06:23:27 GMT

Hello SalvaC

if you can send me the pbix I can check the thing

Usually, with the new Time INtelligence, it is suggested to avoid using DATEADD to go back one year with the syntax you used and it is suggested to write a code like

CALCULATE([Online Sales] ,DATEADD('Weekly Calendar', -12, MONTH) )

To avoid the side effects of the way the new Time Intelligence works with leap years

anyway I cannot say if this solves or not until I see your pbix

PS I suggest to create a new post on this issue to avoid losing the thread (please @me in it)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Best

Re: Interesting effects with calendar-based time intelligence

SalvaC — Mon, 20 Apr 2026 05:39:55 GMT

Dear FBergamaschi

Sorry for the long delay.

You can find my Power BI files here: https://drive.google.com/drive/folders/1H5uJyXM3egzGdlTG5vb95f3i-_SdQoxH?usp=sharing

The ContosoRetail_Small.pbip contains the version with the issue.

You can find it on the page "Week Calendar Separated".

The ContosoRetail_Sales.pbip contains the working version on the page "Weekly"

I hope that you can find the issue or what I have overlooked, as it really unsettles me.

Kind regards

Salvatore

Re: Interesting effects with calendar-based time intelligence

v-hashadapu — Wed, 29 Apr 2026 05:36:07 GMT

Hi , Thank you for reaching out to the Microsoft Community Forum.

I’ve downloaded and reviewed your files, but they are either in JSON format or as .pbir or .pbip files. Unfortunately, I’m unable to open .pbir or .pbip files as shown in the screenshot and working with JSON is not possible in this case.

To better understand and address your issue, please provide sample data that fully covers your question or problem in a usable format, ideally as tables or Excel files, along with all relevant details as text. If needed, include screenshots.

Please make sure to clearly describe your scenario and issue. Do not include any sensitive or unrelated information. Also, please show the expected outcome based on your sample data.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Re: Interesting effects with calendar-based time intelligence

v-hashadapu — Tue, 05 May 2026 08:33:40 GMT

Hi SalvaC , It’s been some time since we last connected. We’re ready to assist you in resolving your issue, but we’ll need the necessary details to continue. Please share the necessary information and we’ll try and help you.

Re: Interesting effects with calendar-based time intelligence

SalvaC — Tue, 12 May 2026 05:48:08 GMT

Dear v-hashadapu

Sorry for the delay.

I was on holiday, and afterwards I was sick.

I uploaded the same as PBIX files.

I can open these files on two different machines.

I have this version installed:

And these Preview features enabled:

Thank you for your support.

Kind regards

Salvatore

Re: Interesting effects with calendar-based time intelligence

v-hashadapu — Wed, 13 May 2026 06:45:19 GMT

Hi SalvaC ,

The issue is caused by calendar-based time intelligence using an incomplete filter context at higher hierarchy levels combined with how the tagged calendar columns are interpreted internally. With the new calendar-based TI, Power BI does not behave like classic date intelligence.
When you write "DATEADD('Weekly Calendar', -1, YEAR)", you are passing the entire tagged calendar, not a single date column.

Please try below steps to fix the issue.

1. Create a NEW WeekKey column in Weekly Dates table with below code.

WeekKey_Fixed =
'Weekly Dates'[YearOfWeek] * 100 +
'Weekly Dates'[Week]

ex: Year Week WeekKey_Fixed
2022 1 202201

Note: This makes every week globally unique.

2. Replace the Calendar Category, Open metadata settings, Go to: Model View --> Weekly Dates table

Current: Week Category --> WeekKey

Replace with: Week Category --> WeekKey_Fixed

3. Mark it as Date table. Table tools --> Mark as date table and choose [Date] column.

Rebuild the hierarchy, Delete the old hierarchy and recreate:

YearOfWeek
WeekKey_Fixed
Date

An then refresh the model.

4. In Table view --> Table tools --> Calendar options --> Weekly calendar.
select 3 dots.

For Week category the primary column is WeekKey replace it with WeekKey_Fixed and save & close. And refresh the model.

5. Update your "Online Sales (PY Weekly)" measure with below.

Online Sales (PY Weekly) =
CALCULATE(
[Online Sales],
DATEADD('Weekly Dates'[Date], -1, YEAR)
)

And also replace you measures with below.

Online Sales (MTD Weekly) =
CALCULATE(
[Online Sales],
DATESMTD('Date'[Date])
)

Online Sales (YTD) =
CALCULATE(
[Online Sales],
DATESYTD('Date'[Date])
)

Note: New Calendar-Based TI requires tagged calendar and full category metadata.

Re: Interesting effects with calendar-based time intelligence

v-hashadapu — Mon, 18 May 2026 06:31:57 GMT

Hi SalvaC , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.

Re: Interesting effects with calendar-based time intelligence

SalvaC — Mon, 18 May 2026 07:17:16 GMT

Thank you for your input, v-hashadapu

We had four days off last week due to public holidays.

But the issue is not that it doesn't work.

The issue is that it doesn't work in one datamodel (ContosoRetail_Small) but does with the identical setup in the new datamodel (ContosoRetail_Sales).

Regarding your suggestion to add a key column to uniquely identify weeks with the year, I already have it:

Here, the data view:

And I did it in the same way in both datamodels.

Kind regards

Salvatore

Re: Interesting effects with calendar-based time intelligence

SalvaC — Mon, 18 May 2026 07:21:00 GMT

Dear FBergamaschi

I come back to your suggestion to use -12 months to go back by one year.

This makes no sense with a weekly calendar, since the months don't align with the weeks.

Therefore, tagging the month in a weekly calendar is not feasible.

I added a new answer to v-hashadapu explaining the issue.

Kind regards

Salvatore

Re: Interesting effects with calendar-based time intelligence

v-hashadapu — Thu, 28 May 2026 10:20:39 GMT

Hi SalvaC , Please update your "Online Sales PY Weekly-Dates" measure with this DAX:

Online Sales PY Weekly-Dates = CALCULATE([Sum Online Sales],

DATEADD('Weekly-Dates Calendar', -1, YEAR)

)

Re: Interesting effects with calendar-based time intelligence

SalvaC — Thu, 28 May 2026 11:36:33 GMT

Hi v-hashadapu

Thank you.

I changed it, but it doesn't change the behaviour.

Look at the following screenshot with the expanded 2023.

You see data for PY 2024:

When I collapse 2023, the PY values in 2024 disappear:

This makes no sense at all.

BTW: I have data for 2022 as well, wich are not shown until I include and expand 2022.

This works with the new datamodel.

Kind regards

Salvatore

Re: Interesting effects with calendar-based time intelligence

v-hashadapu — Mon, 01 Jun 2026 12:17:12 GMT

Hi SalvaC ,

when you expanding 2023, it changes the PY results of 2024. It is a bug in the new calendar-based DATEADD() implementation for custom weekly calendars in that specific semantic model. Instead of using calendar-based time intelligence, better to use Classic Time Intelligence. Please try below measure.

Online Sales PY Weekly-Dates =
CALCULATE(
[Sum Online Sales],
DATEADD('Week-Date Calendar'[Date], -1, YEAR)
)

The new calendar-based TI behaves differently because it works from the tagged calendar and its current filter context.

If you are still experiencing the issue, as you mentioned, the new datamodel (ContosoRetail_Sale) is working fine, but the problem persists with the old datamodel (ContosoRetail_Small). Please update the Date hierarchy fields in the old datamodel to match those in the new datamodel. Refer to the screenshots above for guidance.

Re: Interesting effects with calendar-based time intelligence

SalvaC — Thu, 04 Jun 2026 10:13:25 GMT

Dear v-hashadapu

I built other new datamodels with the same logic, and they work flawlessly,

It seems there is something wrong with the old datamodel, and I will continue working with the new one.

I hoped to find out what's going on in the old one, as I already use the same columns.

Please note that the base field of the Year column in the hierarchy of the new datamodel is the same column as in the old datamodel. It's just renamed:

Thank you very much for your effort and help.

Kind regards

Salvatore

Re: Interesting effects with calendar-based time intelligence

masonreed11t — Tue, 09 Jun 2026 04:02:31 GMT

Thanks for sharing, Salvatore. The leap year and weekly calculation examples are especially insightful. A useful read for anyone working with calendar-based time intelligence in tabular models.