Efficiency of USERELATIONSHIP vs DATEADD for Time Intelligence
Hi,
I am newer to PBI and am creating a dashboard in which a user will select a date from a slicer and I need to have a measure that calculates sales for a comparison date for the date that the user selects.
These comparison dates are pre-defined. For leap years, the comparison date will be the selected date - 371 days and for non-leap years, it will be the selected date - 364 days.
I'm trying to find the most efficient way to do this. Currently, I have a date dimension table that is used for the slicer and the measure is:
PY Net Sales = IF(SELECTEDVALUE(dimDate[Jump Year Flag])="Jump Year",
CALCULATE([CY Net Sales],DATEADD(dimDate[date],-371,DAY)),
CALCULATE([CY Net Sales],DATEADD(dimDate[date],-364,DAY)))
Alternatively, I was considering having a small bridge table (couple hundred rows, 2 columns) that maps a given date to its comparison date in the prior year. My thought was to build a slicer off of the bridge table. The bridge table would have a 1 to 1 active relationship with the dimDate table and would filter the dimDate table. The thought was to have an inactive relationship between the comparison date in the bridge table to the date in the date table and then my PY measure would look like:
PY Net Sales = CALCULATE([CY Net Sales],
USERELATIONSHIP(dimDate[dimDateKey], bridgeDateCompDate[CompDate]))
Which option would result in a faster-loading dashboard for users? Is there a better option I am not considering? Thanks!
Posted Oct 22, 2025, 5:17 PM UTC