Forum Discussion
Sameperiodlastyear on multiple columns
Hi JGravelle ,
Thanks for the clarification. Since you want both the Accounting Date (active relationship) and the Transaction Date (inactive relationship) to shift one year back, relying on SAMEPERIODLASTYEAR alone won’t achieve that. It only affects the active date context.
A common pattern for this scenario is to shift the calendar using DATEADD, and then activate the Transaction Date relationship inside the calculation.
VAR ShiftedCalendar = DATEADD(dim_date[Date], -1, YEAR) RETURN CALCULATE( SUM(fact_amount[Amount]), ShiftedCalendar, USERELATIONSHIP(fact_amount[Transaction Date], dim_date[Date]) )This gives you more control over both date contexts. Since every model behaves differently, please test it in your PBIX if the result still doesn’t align with what you expect, feel free to share another screenshot and we can fine tune it together.
Hope this helps.
Super UserHi JGravelle
SAMEPERIODLASTYEAR shifts the dates a year back relative to the current row date - not a year forward. Try using DATEADD instead.
I'm sorry I don't think I was clear enough in the request.
I want the 12/2/2025, Prior year number to be 1100 which is the same transaction date in the prior year. but I also want the accounting period to shift a year as well, so accounting period 2025.
I want both date periods to shift a year prior.
V-yubandi-msftCommunity Support
Hi JGravelle ,
Thanks for the clarification. Since you want both the Accounting Date (active relationship) and the Transaction Date (inactive relationship) to shift one year back, relying on SAMEPERIODLASTYEAR alone won’t achieve that. It only affects the active date context.
A common pattern for this scenario is to shift the calendar using DATEADD, and then activate the Transaction Date relationship inside the calculation.
VAR ShiftedCalendar = DATEADD(dim_date[Date], -1, YEAR) RETURN CALCULATE( SUM(fact_amount[Amount]), ShiftedCalendar, USERELATIONSHIP(fact_amount[Transaction Date], dim_date[Date]) )This gives you more control over both date contexts. Since every model behaves differently, please test it in your PBIX if the result still doesn’t align with what you expect, feel free to share another screenshot and we can fine tune it together.
Hope this helps.