Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
amabrenda1986
Resolver I
Resolver I

Dynamic Holiday Calendar using Power Query not DAX

Hello

I have a data that spans from 2019 - 2024. I have used start and end date parameter to create a date table.

 

The table below is the holiday table and do not want to manually create these dates but the system to auto-generate and apply some logic. 

Specifically, I want to use the information in the table below and the start and end date to generate a holiday table.

 

Also, I want that anytime the holiday dates fall as Saturday or Sunday, it should be changed to a Monday date of that year.  

HolidayDay
New Year's DayJan 1
Family DayThird Monday
Good FridaySecond Friday
Victoria Day20 May
Canada DayJuly 1
Labour DaySept 2
Thanksgiving DayOct 14
Rememberance DayNov 11
Christmas Day25 Dec

 

Please how can i acheive this using Power Query not DAX? Thanks in advance

1 ACCEPTED SOLUTION
amabrenda1986
Resolver I
Resolver I

I have solved this. by following the steps below. it is pretty long but I had to figure out a way when I got no response.

 

1. Used the date start and end date to create a date table.

2. Used the Add column to add weeek of month, month name, day name, Month & Day

3. I merged columns as follows:

a. Month and Day

b. week of month, day name and month

4. Used conditional column to tell the system when to display any of the above stated holiday names

5. Given that some of the days were Saturday or Sunday, I added a custom column to replace the date using the logic below:

a. For Sunday, I used - if Text.Contains ([Day Name],"Sunday") then Date.AddDays([Date],1) else ""

b. For Saturday, I used - if Text.Contains ([Day Name],"Sunday") then Date.AddDays([Date],2) else ""

6. I merged #5a&b

7. Used Conditional column to replace all null dates with date column and others with value from #6.

Please ensure to always convert these actions to date

View solution in original post

4 REPLIES 4
ronrsnfld
Super User
Super User

There is a web site: Office Holidays that has a list of all holidays for specific countries.

Here is M-Code that will download the full list for Canada. Then it is a matter of filtering the output for those holidays that you want.

 

The "in lieu" holidays are the dates for celebrating a holiday on Monday instead of the weekend day. Apparently, these are not included until Christmas day 2021, but you could change the filtering algorithm to return the "in lieu" date if present, otherwise the actual date. I just selected both in the filter.

 

However, your Good Friday algorithm seems to be incorrect as that should be the Friday before Easter, which is a difficult algorithm to implement in code.

let

//I used parameters for "Start Year", "End Year" and "Country", but you could hard-code those values

//Create list of all dates
    y1 = #date(#"Start Year",1,1),
    y2 =#date(#"End Year",12,31),
    allDates = List.Dates(y1,Duration.TotalDays(y2-y1)+1,#duration(1,0,0,0)),
    
//Create list of country specific Holidays
    yrs = {#"Start Year"..#"End Year"},
    holidays = List.Accumulate(yrs,#table({},{}),(state,current) =>
         let 
            Source=Web.Page(Web.Contents("https://www.officeholidays.com/countries/" & Country & "/" & Number.ToText(current))),
            Data0 = Source{0}[Data],
            DateCol = Table.SelectColumns(Data0,{"Date","Holiday Name"}),
            fullDate = Table.TransformColumns(DateCol, {"Date",each Date.FromText(_ & " " & Number.ToText(current)), type date})
        in   
            state & Table.Distinct(fullDate)),
    #"Filtered Rows" = Table.SelectRows(holidays, each ([Holiday Name] = "Canada Day" or [Holiday Name] = "Canada Day (in lieu)" or [Holiday Name] = "Christmas Day" or [Holiday Name] = "Christmas Day (in lieu)" or [Holiday Name] = "Family Day" or [Holiday Name] = "Good Friday" or [Holiday Name] = "Labour Day" or [Holiday Name] = "New Year's Day" or [Holiday Name] = "New Year's Day (in lieu)" or [Holiday Name] = "Remembrance Day" or [Holiday Name] = "Remembrance Day (in lieu)" or [Holiday Name] = "Thanksgiving" or [Holiday Name] = "Victoria Day"))
in
    #"Filtered Rows"

Results for 2019-2024

ronrsnfld_0-1716463088255.png

 

 

amabrenda1986
Resolver I
Resolver I

I have solved this. by following the steps below. it is pretty long but I had to figure out a way when I got no response.

 

1. Used the date start and end date to create a date table.

2. Used the Add column to add weeek of month, month name, day name, Month & Day

3. I merged columns as follows:

a. Month and Day

b. week of month, day name and month

4. Used conditional column to tell the system when to display any of the above stated holiday names

5. Given that some of the days were Saturday or Sunday, I added a custom column to replace the date using the logic below:

a. For Sunday, I used - if Text.Contains ([Day Name],"Sunday") then Date.AddDays([Date],1) else ""

b. For Saturday, I used - if Text.Contains ([Day Name],"Sunday") then Date.AddDays([Date],2) else ""

6. I merged #5a&b

7. Used Conditional column to replace all null dates with date column and others with value from #6.

Please ensure to always convert these actions to date

AnalyticPulse
Impactful Individual
Impactful Individual

hello @amabrenda1986 

below dax will resolve your issue:
HolidayTable =
ADDCOLUMNS(
CALENDAR([Start Date], [End Date]),
"Holiday",
SWITCH(
TRUE(),
MONTH([Date]) = 1 && DAY([Date]) = 1, "New Year's Day",
MONTH([Date]) = 2 && WEEKDAY(DATE(YEAR([Date]), 2, 1)) = 2, "Family Day", # Third Monday of February
MONTH([Date]) = 3 && WEEKDAY([Date] - DAY([Date]) + 13) = 6, "Good Friday", # Second Friday before Easter Sunday
MONTH([Date]) = 5 && DAY([Date]) = 20, "Victoria Day",
MONTH([Date]) = 7 && DAY([Date]) = 1, "Canada Day",
MONTH([Date]) = 9 && DAY([Date]) = 2, "Labour Day",
MONTH([Date]) = 10 && WEEKDAY(DATE(YEAR([Date]), 10, 1)) = 2 && DAY([Date]) > 7 && DAY([Date]) <= 14, "Thanksgiving Day", # Second Monday of October
MONTH([Date]) = 11 && DAY([Date]) = 11, "Rememberance Day",
MONTH([Date]) = 12 && DAY([Date]) = 25, "Christmas Day",
BLANK()
)
)


just provide the start date and end date of your dataset.

Learn Power BI free:

https://analyticpulse.blogspot.com

Learn Power BI free

Powerbi Visualisation

 

AnalyticPulse_0-1715829624787.png

 

Thanks so much for this but I am looking for a power query solution not DAX. 

 

Once again thank you

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors