Forum Discussion

Advocate III

4 months ago

Solved

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 lea...

"DAX"

"Time Intelligence"

Custom calendar

v-hashadapu
3 months ago
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.

SalvaC

Advocate III

4 months ago

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

Community Support

4 months ago

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
Advocate III
4 months ago
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
  Community Support
  4 months ago
  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.
  - SalvaC
    Advocate III
    3 months ago
    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