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 a table where all the Easter dates have been brought out on rows, but I can't seem to figure out how to make it work as a LY measure (it has to be a measure not a column, because it needs to be dynamic).

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I have the data in one big table which has a Single relationship with a Calendar table. And currently these Easter dates have just been connected to the Calendar table by date.

 

If anybody knows a good solution for this, I would really appreciate it.

 

Thanks!

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

7 Replies

    • 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