Forum Discussion
Interesting effects with calendar-based time intelligence
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
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.
18 Replies
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
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
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
F
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
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.
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
F
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
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.
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.
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.
