Canada holiday data site?

[Keith]

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

[BrianJ]

@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)

Worldwide Public Holidays - Nager.Date

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.

Problem of the Week #1 (DAX) - Perpetual Holiday Calendar

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 measu…

[Keith]

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

[BrianJ]

@Keith ,

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…

Creating A Holiday Calendar Table In Power BI | Enterprise DNA

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

• Brian