Forum Discussion

catleen's avatar
catleen
Frequent Visitor
8 years ago
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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

Mah_87's avatar
Mah_87
Frequent Visitor
3 years ago

Hi
when I have only Easter on my table is fine, but when I expand the table to have other holidays I got this error. any idea how to fix it?
Thanks in advance