Hi there

I was wondering if any of Canadian members have a web site that can share where to get the data set for Canada’s holidays.

I can’t find a web site that has all the information that can automatically download

thanks
Keith

Oddly, I’ve done a lot of work in this area. Holidays are tricky. There are three main ways to determine holidays:

1. pull from a from a website with fixed dates for a given year
2. create a perpetual calendar based on rules for a given country
3. 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.

1 Like

Hi Brian,

Thank you so much for the information. I will check the information out and see what i can figure out.

I want to incorporate holidays into my date table.

My game plan is to create a power bi file (template) that is already set up with date table so i don’t have to do the setup all the time.

thanks again
Keith

PS FYI Problem of the Week #12 isn’t in the Problem of the Week in the online courses

Yup, that’s the way to go. First create your holiday table, and then enter the information from that into the parameters for @Melissa’s Extended Date Table, and then her date table will accurately reflect in the relevant fields your holiday dates.

Here’s my blog entry on creating the holiday table, with a link to the first video I did for ENDA…

This should give you what you need, but if you have any trouble you know where to find me…

• Brian
1 Like