Forum Discussion
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
- MadBern85
Helper 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 🙂