Forum Discussion
Solved
Last Year calculation for Easter
Hi, Does anybody know how it would be possible to calculate Last Year values for holidays where the date varies by year, for example Easter (e.g. 01.04.2018, 16.04.2017, 27.03.2016). I have made ...
HI catleen,
You can use below formula to get previous easter sale based on current date:
Easter Sales LY = VAR currentDate = SELECTEDVALUE ( 'Date'[Date] ) VAR prevEaster = CALCULATE ( VALUES ( Holiday[Date] ), YEAR ( Holiday[Date] ) = YEAR ( currentDate ) - 1 ) RETURN CALCULATE ( SUM ( Sales[Sales] ), 'Date'[Date] = prevEaster )Regards,
Xiaoxin Sheng
Hi Anonymous
Thanks a lot for your solution. I tried using the function VALUES when creating the easterlist and calendarRange variables, but for some reason I can't seem to get the measure working. Would you have time to check what I did wrong here (I added the sample file here: https://1drv.ms/u/s!AnhuzXNFBLQccok09SC8TSzPlr0 )?
Thanks!
HI catleen,
You can use below formula to get previous easter sale based on current date:
Easter Sales LY =
VAR currentDate =
SELECTEDVALUE ( 'Date'[Date] )
VAR prevEaster =
CALCULATE (
VALUES ( Holiday[Date] ),
YEAR ( Holiday[Date] )
= YEAR ( currentDate ) - 1
)
RETURN
CALCULATE ( SUM ( Sales[Sales] ), 'Date'[Date] = prevEaster )
Regards,
Xiaoxin Sheng
