Forum Discussion

MadBern85's avatar
MadBern85
Icon for Helper I rankHelper I
1 year ago
Solved

Issue with SAMEPERIODLASTYEAR() and Date presets table

Hi
I have made a table for date presets in my PBI-model, containing dates for "yesterday", "Current week", "Last week", etc.

This is then connected to my calendar table on date values (many-to-one), but since the presets table contains duplicate dates, the relationship between then has to be bidirectional.

In most cases this works fine, and my users have a more user friendly way of slicing reports.

 

However, this bidirectional relationship causes problems when using SAMEPERIODLASTYEAR(), even if it's not directly involved in the measure. Ex:
CALCULATE(
SUM(fact_Table[Cost]),
SAMEPERIODLASTYEAR(Calendar[Date])
)

Does anyone have any good solutions or alternatives in this case? By either changing the measure to ignore that relationship or setting up a date presets table that doesn't need a bidirectional relationship?

  • Hi MadBern85 

     

    Instead of a bi-direcitonal relationship, I would use many to many and set the relationship to flow from the presets to the actual calendar table. If that still doensn't work,  include REMOVEFILTERS(presetstable) to your formula.

2 Replies

  • Hi MadBern85 

     

    Instead of a bi-direcitonal relationship, I would use many to many and set the relationship to flow from the presets to the actual calendar table. If that still doensn't work,  include REMOVEFILTERS(presetstable) to your formula.

    • MadBern85's avatar
      MadBern85
      Icon for Helper I rankHelper I

      Hi
      REMOVEFILTERS() have been tried already, without effect.
      But changing to a many-to-many (which I of course hadn't tried already) seems to do the trick.

      Thanks a bunch 🙂