I would like to create a perpetual calendar in Power BI (Power Query) with the holidays. The thing is, I live in Canada and Easter and Victoria Day are quite complicated to integrate in this kind of calendar.
For the perpetual calendar, I used this technique (you can find the code on this website), which is very effective. It's "simply" a function to which you tell that you want a calendar from this date to this date and it generates everything you want and probably more. However, the calendar doesn't know which day is a holiday. So you need to have another request in Power BI with all the holidays for that period. Then you tell the calendar to use this holiday request as a reference point and then it will know which day is a holiday.
This is where it gets very complicated. Some holidays are based on a specific date, some are based on a specific day and others vary a lot.
Based on a specific date : New Year's Day, January 1st ; Saint-Jean-Baptiste Day, June 24 ; Canada Day, July 1st ; National Day for Thruth and Reconciliation, September 30 ; Remembrance Day, November 11 ; Christmas Day, December 25 ; Boxing Day, December 26.
Based on a specific day : Labour day, first Monday of September ; Thanksgiving, second Monday of October.
Those that vary a lot : Good Friday, Friday before Easter ; Easter, first Sunday after the Paschal full moon ; Easter Monday, Monday after Easter ; Victoria Day, last Monday preceding May 25.
Finally, some holidays happen only once to commemorate specific events like the death of the Queen on September 19, 2022.
I would like to create a function to which I indicate that I want all holiday for a specific period and it generates everything by itself. Then I could tell my calendar function to use it as a reference to know which day is a holiday. It would also be great if I could add some new holiday here and there for specific events like the death of the Queen.
For holidays like New Year's Day, Canada Day and Thanksgiving, I used this tutorial, but it's incomplete. Also, it doesn’t explain how to integrate holidays like Easter or Victoria Day. So this is where I'm stuck right now.
I know it's complicated but if someone can help me, it would be awesome.
As Jon suggest, I think it will be a lot easier to use the canada-holidays.ca/api.
I'm having issues with this API though. If I want multiple years in a single request, I need to create a request for each year and then append them. I'm trying to find a way to only have a single request but I'm struggling with this as well.
As soon as I find a solution, I'll update this post.
EDIT
Someone help me with that on another post. You can find the solution right here!