Oddly, I’ve done a lot of work in this area. Holidays are tricky. There are three main ways to determine holidays:
- pull from a from a website with fixed dates for a given year
- create a perpetual calendar based on rules for a given country
- pull holidays dynamically from an API
If you just need a single year or two, method one is the easiest. If you’re going to be doing a lot of this for multiple countries over a number of years, method three is probably best. For a single country over numerous years I use method two. Below are links in order that will help you with each of the three methods.
If you go with method two (probably my preferred choice in most instances that I encounter), check out EDNA Problem of the Week #1 and #12. The first one shows how to create a perpetual calendar using DAX, #12 shows how to do it using PQ (the better approach, IMO)
https://www.vpcalendar.net/Holiday_Dates/Holiday_Determinations.html
https://www.programmableweb.com/news/8-top-apis-holidays/brief/2019/10/20
Probably more than you wanted to know, but I hope that’s helpful.
- Brian
P.S. from a rule-based standpoint, Easter is an absolute nightmare (Easter Day is the first Sunday after the first full moon after 21 March). In response to POTW #1, @chrish had a brilliant post that explained how to do that calculation in DAX.