I went down the calculated column route basing my solution of replicating how I normally use a Holiday Table in Power Query and pull in to the Date Table to enable filtering by that column. Definitely not the most efficient method but it worked for my brain as a starting point.
I could see that there was a pattern in the dates with some being fixed, eg, 25th December and some being relative and would therefore need to be treated differently.
I used Filter as an iterator to run through each row of the table using Var a to specify the row to consider. I then used conditions in the filter specified to check whether the current row met the criteria.
So for Labor day, the months = “September” and using Day[Date] to be less than 8 this would be the first week. Weekday[Date] allowed me to specify the day of the week.
For those dates with a fixed day, eg, Christmas I could specify the day using the Day([Date]) to equal a certain number such as 25.
This method works for first, second, third and fourth week but not last. Memorial Day is the last week in May which has 31 Days so I used that as a variable and changed the code for Day Date to be between 31 - 7 and 31.
To create the column to offset the holiday if on a weekend, I used Dates Day of Week and if a 6 then go back a day by using Date - 1, if a 7 then go forward.
Off Set =
IF(‘Dates’[DayOfWeek] = 6, ‘Dates’[Date] -1,
IF(‘Dates’[DayOfWeek] = 0 , ‘Dates’[Date] + 1)))
I’ve attached my pbix and you’ll see that I’ve been through Melissa’s solution as a measure and commented everything as I wanted to understand her solution. I haven’t used a lot of DAX for text extraction so this was really interesting.
eDNA Problem of the Week 1 – Perpetual Holiday Calendar DAX- Dec 2020.pbix (1.4 MB)