I’m sorry that I had missed the first Problem of the week - but thought that I would take the opportunity of Christmas holidays to give it a go and review other solutions.
I found the pure DAX measures approach in Brian and other’s solution’s quite complex and instead used a calculated column in the dates table to identify the holidays to their respective days (which dealt with all of the tricky First, Third, Fourth, Last of the month parsing quite efficiently) and then a couple of simple measures to pass the Holiday Date and the Celebrated on date to the solution matrix (I adapted the matrix so that I could see holidays for all years in a single visual). This got me to a solution for the US holiday problem:
But this got me thinking about how to adapt this approach to non-US holidays - specifically UK holidays. Many of these holidays are amenable to the same approach as the US holidays (ie first Monday in May, Last Mondays in May and August), but the one that really got me thinking was how to determine Good Friday and Easter Monday which move throughout March and April depending on the date of Easter. This sucked me into 2000 years of (the still unresolved) question of how to calculate the date of Easter (for those who like that sort of thing the Wikipedia article on the Easter Computus is interesting: https://en.wikipedia.org/wiki/Computus ). In short, in the western Christian tradition, Easter Day is the first Sunday after the first full moon after 21 March.
So I created an Easter computus to find this date as a separate calculated column in the dates table. The method I used is based on the Ronald W Mallen method set out at: https://www.assa.org.au/resources/more-articles/easter-dating-method/ .
I adjusted this method to work with DAX and the columns that were already in the date table. Key steps were:
- calculating the number of days that the first full moon falls after 21 March - this runs on a 19 year cycle for the period 1900 - 2199 which is good enough for my purposes.
- calculating the number of days from the full moon to the following Sunday.
Once I had finished the Easter computus, the rest of the UK holiday calculations could be done like the US ones - one small nuance is that if a UK fixed date holiday (ie New Years Day, Christmas or Boxing Day) falls on a weekend it is always taken on the next working day - this might not be the following Monday if that day is already a holiday. This gave me the UK perpetual holiday calendar:
The code for this is in the attached PBI file
eDNA Problem of the Week 1 – ChrisH Perpetual Holiday Calendar US & UK.pbix (930.9 KB)
I would welcome any comments on or improvements to my methodology for the Easter computus. Or if there are any holiday dates in your country or faith tradition that could throw up some interesting calculation challenges, I’d love to hear them.
Happy holidays,
Chris