Forum Discussion
Advocate IIIInteresting effects with calendar-based time intelligence
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.
Community SupportHi 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.
Advocate IIIThank 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
- SalvaC
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
- v-hashadapu
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)
)
- SalvaC
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
- v-hashadapu
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.