Last Year calculation for Easter

[catleen]

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!

[v-shex-msft]

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]

Hi @v-shex-msft,

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.

[v-shex-msft]

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]

Hi @v-shex-msft

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!

[v-shex-msft · Accepted solution]

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]

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

[catleen]

This works! Thank you so much @v-shex-msft.