Interesting effects with calendar-based time intelligence

[SalvaC]

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

[v-hashadapu]

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.

[SalvaC]

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.

[FBergamaschi]

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

[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-hashadapu]

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

[v-hashadapu]

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.

[FBergamaschi]

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

[SalvaC]

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

[FBergamaschi]

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