Prior Year Measure

[dokat]

Hi,

I have below sample table where i am trying to calculate YoY Volume Sales difference. I tried below formula however it sums up all the values between 2020 thru 2017, not just 2020 value.

2020 = CALCULATE(SUM('P&L'[Values]), SAMEPERIODLASTYEAR('P&L'[CALENDAR YEAR]))

2019 = CALCULATE(SUM('P&L'[Values]), SAMEPERIODLASTYEAR('P&L'[CALENDAR YEAR],-2,year))

Can anyone have a solve for this problen?

Calendar Year	Customer	P&L	Values
2017	ACME MARKETS	Volume Sales	300396404
2018	ACME MARKETS	Volume Sales	323496404
2019	ACME MARKETS	Volume Sales	457496404
2020	ACME MARKETS	Volume Sales	789189639
2021	ACME MARKETS	Volume Sales	679762927

[bcdobbs · Accepted solution]

Hi,

SAMEPERIODLASTYEAR works with dates ideally in a date table. You however have data already agregated at the year level.

So to get the data for 2020 you can just do:

2020Vals = 
	CALCULATE(
		SUM('P&L'[Values]), 
		'P&L'[CALENDAR YEAR] = 2020
	)

For something a bit more dynamic:

PreviousYearVals = 

VAR LatestVisibleYear = 
	MAX ( 'P&L'[CALENDAR YEAR] )

RETURN
	CALCULATE(
		SUM('P&L'[Values]), 
		'P&L'[CALENDAR YEAR] = LatestVisibleYear - 1
	)