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,

 

Maybe you can take a look at following link to know how to calculate easter, then convert them to measure formula.

Calculate the Date of Easter Sunday

 

Regards,

Xiaoxin Sheng

catleen's avatar
catleen
Frequent Visitor
8 years ago

Hi Anonymous,

Thanks for the answer. I already have the Easter dates in a table. I am looking for help on how to use these for calculating the LY measure (so I could use the similar method for other holidays whose dates are variable as well)

It should be something like custom Year-over-Year calculation. I actually found a similar idea here: https://www.sqlbi.com/articles/custom-year-over-year-calculation-in-dax/ but I can't seem to figure out how to make it applicable for my case.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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 )
        )
    )

     

    The IN operator in DAX

     

    Regards,
    Xiaoxin Sheng

    • catleen's avatar
      catleen
      Frequent Visitor
      8 years ago

      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!

      • 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