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.
Advocate IIIGood 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.
Super UserHello 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
F
- SalvaC
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
v-hashadapuCommunity Support
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-...
- SalvaC
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
- SalvaC
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