Table of Contents


Overview

Many companies require reporting weekends and holidays as the previous business or working day. So how does one create a business reporting calendar completely in Power Query / PowerBI using M-Functions?
The process uses three main object: holiday table, date table, and a function. The sample code shows a hard coded holiday table, but it may come from another source. The date table is dynamically generated; the dates can be changed by altering the beginning date, ending date, or duration.

Then weekends and holidays are identified in the date table, followed by identifying work days. Finally, previous work day is calculated by a function that consumes the overall date table.

Process

1) Create a CalendarHolidays - the Holiday table used for exclusions

The following are contained in CalendarSource - the calendar table for all dates

2) Create a date table - calculation will be 'inline' with this table
3) Determine weekends: IsWeekend
4) Determine holidays: IsHoliday - an intermediate step is included to show the holiday name
5) Determine work day: IsWorkDay
6) Create a function that returns the previous work day; this function consumes the date table and 'current' date

The final query has two date columns:
EventDate - unique date column for joining and calendar table attributes
PreviousWorkDate - column for presentation and reporting

A note about the function, if the calendar table's first day is not a work day, then those non-work days are returned as the first work day in the calendar. This is shown in April 2017.

Code

Query: CalendarHolidays


let
    Source = Table.TransformColumnTypes
    (
        Table.FromRows({
            {"2017-01-01", "New Year's Day"},
            {"2017-01-02", "New Year's Day - Observed"},
            {"2017-01-16", "Martin Luther King, Jr. Day"},
            {"2017-02-15", "Presidents' Day"},
            {"2017-05-29", "Memorial Day"},
            {"2017-07-04", "Independence Day"},
            {"2017-09-04", "Labor Day"},
            {"2017-11-23", "Thanksgiving Day"},
            {"2017-11-24", "Day after Thanksgiving"},
            {"2017-12-25", "Christmas Day"}
        },
        {"HolidayDate", "HolidayName"}),
        {{"HolidayDate", type date}, {"HolidayName", type text}}
    )
in
    Source

Query: CalendarSource


let
    // Set date range
    DateBeg         = DateTime.Date(Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(), 0))),
    DateEnd         = DateTime.Date(Date.EndOfMonth(DateTime.LocalNow())),
    DurationDays    = Duration.Days(Duration.From(DateEnd-DateBeg)) + 1,
 
    // Generate list of dates then convert to typed table
    DateList        = List.Dates(DateTime.Date(DateBeg), DurationDays, #duration(1, 0, 0, 0)),
    DateTable       = Table.FromList(DateList, Splitter.SplitByNothing(), {"EventDate"}, ExtraValues.Error),
    DateTableTyped  = Table.TransformColumnTypes(DateTable, {{"EventDate", type date}}),
 
    // CalendarTable is consumed the Typed Data Table; this short ETL line allows for
    // changing the dates from being computed to a calendar source
    CalendarTable   = DateTableTyped,
 
    // Determine if date is a weekend
    AddIsWeekend    = Table.AddColumn(CalendarTable, "IsWeekend", each if (Date.DayOfWeek([EventDate], Day.Monday) >= 5) then 1 else 0, Int32.Type),
 
    // Determine if date is a holiday
    AddHolidayName  = Table.ExpandTableColumn(Table.NestedJoin(AddIsWeekend, {"EventDate"}, CalendarHolidays, {"HolidayDate"}, "HolidayName", JoinKind.LeftOuter), "HolidayName", {"HolidayName"}),   
    AddIsHoliday    = Table.AddColumn(AddHolidayName, "IsHoliday", each if([HolidayName] is null) then 0 else 1, Int32.Type),
 
    // Determine if date is a work day (not weekend, not holiday)
    AddIsWorkDay    = Table.AddColumn(AddIsHoliday, "IsWorkDay", each if(([IsWeekend] + [IsHoliday]) = 0) then 1 else 0, Int32.Type),
 
    // Determine previous work date
    fcnPrevWorkDate = (Calendar as table, CalendarDate as date) as date =>
        let
            fcn = List.Max(Table.SelectRows(Calendar, each ([IsWorkDay] = 1 and [EventDate] <= Date.From(CalendarDate)))[EventDate]),
            fnz = List.Min(Table.SelectRows(Calendar, each ([IsWorkDay] = 1 and [EventDate] >  Date.From(CalendarDate)))[EventDate]),
            ref = if (fcn <> null) then fcn else fnz
        in
            ref,
 
    // Add previous work date
    AddPrevWorkDate = Table.AddColumn(AddIsWorkDay, "PreviousWorkDate", each fcnPrevWorkDate(AddIsWorkDay, [EventDate]), type date)
 
in
    AddPrevWorkDate