PowerBI: Power Query: Report Previous Business day for Non-Working Days

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