Forum Discussion
Last Year calculation for Easter
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 catleen,
I'd like to suggest you use variable to store easter date list, then use in operation which calendar date range and easter date list.(records in calendar date range and not in easter list)
Sample =
VAR easterlist =
VALUE ( Easter[Date] )
VAR calendarRange = 'selected calendar range'
RETURN
CALCULATE (
SUM ( Table[Amount] ),
FILTER (
ALL ( Table ),
Table[Date] IN calendarRange
&& NOT ( Table[Date] IN easterlist )
)
)
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
