Dynamic Holiday Calendar using Power Query not DAX

[amabrenda1986]

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.  

Holiday	Day
New Year's Day	Jan 1
Family Day	Third Monday
Good Friday	Second Friday
Victoria Day	20 May
Canada Day	July 1
Labour Day	Sept 2
Thanksgiving Day	Oct 14
Rememberance Day	Nov 11
Christmas Day	25 Dec

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

[ronrsnfld]

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

[amabrenda1986 · Accepted solution]

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]

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

Commercial Real Estate Portfolio Overview by DataFreedom

Superstore Sales 2022 vs 2023: Year-on-Year Performance Comparison dashboard

Case Study: PowerBI Dashboard Developer for Sales Visualization

Case Study: Banking and Finance dashboard (Step by step guide)

Powerbi Visualisation

[amabrenda1986]

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

Once again thank you