Extended Date table M function | Creates an ISO-8601 type calendar

extendedDateTable.pq

let
    fnDateTable = ( StartDate as date, EndDate as date, optional FYStartMonthNum as number, optional Holidays as list, optional WDStartNum as number, optional AddRelativeNetWorkdays as logical ) as table =>
    let
        FYStartMonth = List.Select({1..12}, each _ = FYStartMonthNum){0}? ?? 1,
        WDStart = List.Select({0..1}, each _ = WDStartNum){0}? ?? 0,
        CurrentDate = Date.From(DateTimeZone.FixedUtcNow()),
        DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1,
        weekStart = Day.Monday,
        HolidaysProvided = Holidays <> null,

        Source = List.Dates(StartDate, DayCount, Duration.From(1)),
        AddToday = if List.Contains(Source, CurrentDate) then Source else List.Combine({Source, {CurrentDate}}),
        ToTable = Table.FromColumns({AddToday}, type table [Date = date]),

        /*  === Year Columns === */
        AddYearRecord = Table.AddColumn(ToTable, "YearRecord", each [
            Year = Date.Year([Date]),
            CurrYearOffset = Date.Year([Date]) - Date.Year(CurrentDate),
            YearCompleted = Date.EndOfYear([Date]) < Date.EndOfYear(CurrentDate)
        ], type [Year=Int64.Type, CurrYearOffset=Int64.Type, YearCompleted=logical]),
        ExpandedYear = Table.ExpandRecordColumn(AddYearRecord, "YearRecord", {"Year", "CurrYearOffset", "YearCompleted"}),

        /*  === Quarter Columns === */
        AddQuarterRecord = Table.AddColumn(ExpandedYear, "QuarterRecord", each [
            Quarter Number = Date.QuarterOfYear([Date]),
            Quarter = "Q" & Number.ToText(#"Quarter Number"),
            Start of Quarter = Date.StartOfQuarter([Date]),
            End of Quarter = Date.EndOfQuarter([Date]),
            #"Quarter & Year" = "Q" & Number.ToText(#"Quarter Number") & Date.ToText([Date], [Format = " yyyy"]),
            QuarternYear = [Year] * 10 + #"Quarter Number",
            CurrQuarterOffset = ((4 * Date.Year([Date])) + #"Quarter Number") - ((4 * Date.Year(CurrentDate)) + Date.QuarterOfYear(CurrentDate)),
            QuarterCompleted = #"End of Quarter" < Date.EndOfQuarter(CurrentDate)
        ], type [Quarter Number=Int64.Type, Quarter=text, Start of Quarter=date, End of Quarter=date, #"Quarter & Year"=text, QuarternYear=Int64.Type, CurrQuarterOffset=Int64.Type, QuarterCompleted=logical]),
        ExpandedQuarter = Table.ExpandRecordColumn(AddQuarterRecord, "QuarterRecord", 
            {"Quarter Number", "Quarter", "Start of Quarter", "End of Quarter", "Quarter & Year", "QuarternYear", "CurrQuarterOffset", "QuarterCompleted"}),

        /*  === Month Columns === */
        AddMonthRecord = Table.AddColumn(ExpandedQuarter, "MonthRecord", each [
            Month = Date.Month([Date]),
            Start of Month = Date.StartOfMonth([Date]),
            End of Month = Date.EndOfMonth([Date]),
            #"Month & Year" = Text.Proper(Date.ToText([Date], [Format = "MMM yyyy"])),
            MonthnYear = [Year] * 100 + Month,
            CurrMonthOffset = ((12 * Date.Year([Date])) + Month) - ((12 * Date.Year(CurrentDate)) + Date.Month(CurrentDate)),
            MonthCompleted = #"End of Month" < Date.EndOfMonth(CurrentDate),
            Month Name = Text.Proper(Date.ToText([Date], "MMMM")),
            Month Short = Text.Proper(Date.ToText([Date], "MMM")),
            Month Initial = Text.Start(Text.Proper(Date.ToText([Date], "MMMM")), 1) & Text.Repeat(Character.FromNumber(8203), Month),
            Day of Month = Date.Day([Date])
        ], type [Month=Int64.Type, Start of Month=date, End of Month=date, #"Month & Year"=text, MonthnYear=Int64.Type, CurrMonthOffset=Int64.Type, MonthCompleted=logical, Month Name=text, Month Short=text, Month Initial=text, Day of Month=Int64.Type]),
        ExpandedMonth = Table.ExpandRecordColumn(AddMonthRecord, "MonthRecord", 
            {"Month", "Start of Month", "End of Month", "Month & Year", "MonthnYear", "CurrMonthOffset", "MonthCompleted", "Month Name", "Month Short", "Month Initial", "Day of Month"}),

        /*  === Week Columns === */
        AddWeekRecord = Table.AddColumn(ExpandedMonth, "WeekRecord", each [
            WeekNumCalc = Number.RoundDown((Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], weekStart) + 1) + 10) / 7),
            Week Number = if WeekNumCalc = 0 
                then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date]) - 1, 12, 31)) - (Date.DayOfWeek(#date(Date.Year([Date]) - 1, 12, 31), weekStart) + 1) + 10) / 7)
                else if (WeekNumCalc = 53 and (Date.DayOfWeek(#date(Date.Year([Date]), 12, 31), weekStart) + 1 < 4)) then 1 else WeekNumCalc,
            Start of Week = Date.StartOfWeek([Date], weekStart),
            End of Week = Date.EndOfWeek([Date], weekStart),
            #"Week & Year" = "W" & Text.PadStart(Text.From(#"Week Number"), 2, "0") & " " & Text.From(Date.Year(Date.AddDays(#"Start of Week", 3))),
            WeeknYear = Date.Year(Date.AddDays(#"Start of Week", 3)) * 100 + #"Week Number",
            CurrWeekOffset = (Number.From(#"Start of Week") - Number.From(Date.StartOfWeek(CurrentDate, weekStart))) / 7,
            WeekCompleted = #"End of Week" < Date.EndOfWeek(CurrentDate, weekStart)
        ], type [Week Number=Int64.Type, Start of Week=date, End of Week=date, #"Week & Year"=text, WeeknYear=Int64.Type, CurrWeekOffset=Int64.Type, WeekCompleted=logical]),
        ExpandedWeek = Table.ExpandRecordColumn(AddWeekRecord, "WeekRecord", 
            {"Week Number", "Start of Week", "End of Week", "Week & Year", "WeeknYear", "CurrWeekOffset", "WeekCompleted"}),

        /* === Day Columns === */
        AddDayRecord = Table.AddColumn(ExpandedWeek, "DayRecord", each [
            Day of Week Number = Date.DayOfWeek([Date], weekStart) + WDStart,
            Day of Week Name = Text.Proper(Date.ToText([Date], "dddd")),
            Day of Week Initial = Text.Proper(Text.Start(#"Day of Week Name", 1)) & Text.Repeat(Character.FromNumber(8203), Date.DayOfWeek([Date], weekStart) + WDStart),
            Day of Year = Date.DayOfYear([Date]),
            DateInt = [Year] * 10000 + [Month] * 100 + [Day of Month],
            CurrDayOffset = Number.From([Date]) - Number.From(CurrentDate),
            IsAfterToday = not ([Date] <= CurrentDate),
            IsWeekDay = if Date.DayOfWeek([Date], weekStart) > 4 then false else true,
            IsHoliday = if not HolidaysProvided then "Unknown" else List.Contains(Holidays, [Date]),
            IsBusinessDay = if (if Date.DayOfWeek([Date], weekStart) > 4 then false else true) and (if HolidaysProvided then not List.Contains(Holidays, [Date]) else true) then true else false,
            Day Type = if HolidaysProvided and List.Contains(Holidays, [Date]) then "Holiday" 
                else if (if Date.DayOfWeek([Date], weekStart) > 4 then false else true) = false then "Weekend" 
                else if (if Date.DayOfWeek([Date], weekStart) > 4 then false else true) = true then "Weekday" else null
        ], type [Day of Week Number=Int64.Type, Day of Week Name=text, Day of Week Initial=text, Day of Year=Int64.Type, DateInt=Int64.Type, CurrDayOffset=Int64.Type, IsAfterToday=logical, IsWeekDay=logical, IsHoliday= (if HolidaysProvided then Logical.Type else Text.Type), IsBusinessDay=logical, Day Type=text]),
        ExpandedDay = Table.ExpandRecordColumn(AddDayRecord, "DayRecord", 
            {"Day of Week Number", "Day of Week Name", "Day of Week Initial", "Day of Year", "DateInt", "CurrDayOffset", "IsAfterToday", "IsWeekDay", "IsHoliday", "IsBusinessDay", "Day Type"}),

        /* === ISO Columns === */
        InsertISOYear = Table.AddColumn(ExpandedDay, "ISO Year", each Date.Year(Date.AddDays(Date.StartOfWeek([Date], weekStart), 3)), Int64.Type),
        InsertISOqNum = Table.AddColumn(InsertISOYear, "ISO Quarter Number", each if [Week Number] > 39 then 4 else if [Week Number] > 26 then 3 else if [Week Number] > 13 then 2 else 1, Int64.Type),
        InsertISOqtr = Table.AddColumn(InsertISOqNum, "ISO Quarter", each "Q" & Number.ToText([ISO Quarter Number]), type text),
        InsertISOQuarter = Table.AddColumn(InsertISOqtr, "ISO Quarter & Year", each "Q" & Number.ToText([ISO Quarter Number]) & " " & Number.ToText([ISO Year]), type text),
        InsertISOqNy = Table.AddColumn(InsertISOQuarter, "ISO QuarternYear", each [ISO Year] * 10 + [ISO Quarter Number], Int64.Type),

        /* === Fiscal Columns === */
        AddFYnr = Table.AddColumn(InsertISOqNy, "FY number", each (if [Month] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]), Int64.Type),
        AddFY = Table.AddColumn(AddFYnr, "Fiscal Year", each "FY" & Text.From([FY number]), type text),
        AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", each "FQ" & Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 )) & " " & Text.From([FY number]), type text),
        AddFQnYr = Table.AddColumn(AddFQ, "FQuarternYear", each [FY number] * 10 + Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 ), type number),
        AddFM = Table.AddColumn(AddFQnYr, "Fiscal Period Number", each if [Month] >= FYStartMonth and FYStartMonth >1 then [Month] - (FYStartMonth-1) else if [Month] >= FYStartMonth and FYStartMonth =1 then [Month] else [Month] + (12-FYStartMonth+1), type number),
        AddFP = Table.AddColumn(AddFM, "Fiscal Period", each "FP" & Text.PadStart( Text.From([Fiscal Period Number]), 2, "0") & " " & Text.From([FY number]), type text),
        AddFMnYr = Table.AddColumn(AddFP , "FPeriodnYear", each [FY number] * 100 + [Fiscal Period Number], type number),
        FYCalendarStart = #date( Date.Year(StartDate)-1, FYStartMonth, 1 ),
        InsertFFD = Table.AddColumn(AddFMnYr, "FiscalFirstDay", each if [Month] >= FYStartMonth then #date([Year], FYStartMonth, 1) else #date([Year]-1, FYStartMonth, 1), type date),
        InsertFiscalWeekNumber = Table.AddColumn(InsertFFD, "Fiscal Week Number", each let
            FiscalWeekStart = Date.StartOfWeek([FiscalFirstDay], weekStart),
            CurrentWeekStart = Date.StartOfWeek([Date], weekStart),
            WeekDiff = Duration.Days(CurrentWeekStart - FiscalWeekStart) / 7
            in Number.RoundDown(WeekDiff) + 1, Int64.Type
        ),
        FWlogic = List.Contains( {null}, FYStartMonthNum),
        UpdateFYWeek = if FWlogic then Table.ReplaceValue(InsertFiscalWeekNumber, each [Fiscal Week Number], each if FYStartMonth =1 then [Week Number] else [Fiscal Week Number], Replacer.ReplaceValue, {"Fiscal Week Number"}) else InsertFiscalWeekNumber,
        AddFYW = Table.AddColumn( UpdateFYWeek, "Fiscal Week", each if FWlogic then "F" & [#"Week & Year"] else "FW" & Text.PadStart( Text.From([Fiscal Week Number]), 2, "0") & " " & Text.From([FY number]), type text),
        InsertFWeeknYear = Table.AddColumn(AddFYW, "FWeeknYear", each if FWlogic then [WeeknYear] else [FY number] * 100 + [Fiscal Week Number],  Int64.Type),

        /*  === Get Current Date Values === */
        CurrentDateRecord = Table.SelectRows(InsertFWeeknYear, each ([Date] = CurrentDate)),
        CurrentISOyear = CurrentDateRecord{0}[ISO Year],
        CurrentISOqtr = CurrentDateRecord{0}[ISO Quarter Number],
        CurrentYear = CurrentDateRecord{0}[Year],
        CurrentMonth = CurrentDateRecord{0}[Month],
        CurrentFiscalFirstDay = CurrentDateRecord{0}[FiscalFirstDay],
        PrevFiscalFirstDay = Date.AddYears(CurrentFiscalFirstDay, -1),
        CurrentFY = CurrentDateRecord{0}[Fiscal Year],
        CurrentFQ = CurrentDateRecord{0}[FQuarternYear],
        CurrentFP = CurrentDateRecord{0}[FPeriodnYear],
        CurrentFW = CurrentDateRecord{0}[FWeeknYear],

        /*  === Others & Clean up === */
        InsertISOYrOffset = Table.AddColumn(InsertFWeeknYear, "ISO CurrYearOffset", each [ISO Year] - CurrentISOyear, Int64.Type),
        InsertISOQtrOffset = Table.AddColumn(InsertISOYrOffset, "ISO CurrQuarterOffset", each ((4 * [ISO Year]) + [ISO Quarter Number]) - ((4 * CurrentISOyear) + CurrentISOqtr), Int64.Type),
        InsertFYoffset = Table.AddColumn(InsertISOQtrOffset, "Fiscal CurrYearOffset", each try (if [Month] >= FYStartMonth then [Year] + 1 else [Year]) - (if CurrentMonth >= FYStartMonth then CurrentYear + 1 else CurrentYear) otherwise null, Int64.Type),
        InsertCurrentFY = Table.AddColumn(InsertFYoffset, "IsCurrentFY", each if [Fiscal Year] = CurrentFY then true else false, type logical),
        InsertCurrentFQ = Table.RemoveColumns( Table.AddColumn(InsertCurrentFY, "IsCurrentFQ", each if [FQuarternYear] = CurrentFQ then true else false, type logical), {"FY number"} ),
        InsertCurrentFP = Table.AddColumn(InsertCurrentFQ, "IsCurrentFP", each if [FPeriodnYear] = CurrentFP then true else false, type logical),
        InsertCurrentFW = Table.AddColumn(InsertCurrentFP, "IsCurrentFW", each if [FWeeknYear] = CurrentFW then true else false, type logical),
        InsertPYTD = Table.AddColumn(InsertCurrentFW, "IsPYTD", each if CurrentYear-1 = [Year] and [Day of Year] <= CurrentDateRecord{0}[Day of Year] then true else false, type logical),
        InsertPFYTD = Table.AddColumn(InsertPYTD, "IsPFYTD", each if [Fiscal CurrYearOffset] = -1 then Duration.Days([Date] - PrevFiscalFirstDay) + 1 <= Duration.Days(CurrentDate - CurrentFiscalFirstDay) + 1 else false, type logical),
        InsertNetWorkdays = if AddRelativeNetWorkdays = true then Table.AddColumn(InsertPFYTD, "Relative Networkdays", each fxNETWORKDAYS( StartDate, [Date], Holidays ), Int64.Type) else InsertPFYTD,
        fxNETWORKDAYS = (StartDate, EndDate, optional Holidays as list) =>
        let
            ListOfDates = List.Dates( StartDate, Number.From(EndDate-StartDate)+1, Duration.From(1) ),
            DeleteHolidays = if Holidays = null then ListOfDates else List.Difference( ListOfDates, List.Transform(Holidays, Date.From )),
            DeleteWeekends = List.Select( DeleteHolidays, each Date.DayOfWeek( _, weekStart) < 5 ),
            CountDays = List.Count( DeleteWeekends)
        in
            CountDays,
        RemoveToday = Table.RemoveColumns(if not List.Contains(Source, CurrentDate) then Table.SelectRows(InsertNetWorkdays, each ([Date] <> CurrentDate)) else InsertNetWorkdays, {"Day of Year", "FiscalFirstDay"}),
        ChType = Table.TransformColumnTypes(RemoveToday, {{"Year", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month", Int64.Type}, {"Day of Month", Int64.Type}, {"DateInt", Int64.Type}, {"Day of Week Number", Int64.Type}, {"ISO CurrYearOffset", Int64.Type}, {"ISO QuarternYear", Int64.Type}, {"ISO CurrQuarterOffset", Int64.Type}, {"Week Number", Int64.Type}, {"WeeknYear", Int64.Type}, {"MonthnYear", Int64.Type}, {"QuarternYear", Int64.Type}, {"FQuarternYear", Int64.Type}, {"Fiscal Period Number", Int64.Type}, {"FPeriodnYear", Int64.Type}, {"CurrWeekOffset", Int64.Type}, {"CurrMonthOffset", Int64.Type}, {"CurrQuarterOffset", Int64.Type}, {"CurrYearOffset", Int64.Type}, {"Fiscal CurrYearOffset", Int64.Type}, {"Fiscal Week Number", Int64.Type}}),
        ReorderCols = Table.ReorderColumns(ChType, {"Date", "Year", "CurrYearOffset", "YearCompleted", "Quarter Number", "Quarter", "Start of Quarter", "End of Quarter", "Quarter & Year", "QuarternYear", "CurrQuarterOffset", "QuarterCompleted", "Month", "Start of Month", "End of Month", "Month & Year", "MonthnYear", "CurrMonthOffset", "MonthCompleted", "Month Name", "Month Short", "Month Initial", "Day of Month", "Week Number", "Start of Week", "End of Week", "Week & Year", "WeeknYear", "CurrWeekOffset", "WeekCompleted", "Day of Week Number", "Day of Week Name", "Day of Week Initial", "DateInt", "CurrDayOffset", "IsAfterToday", "IsWeekDay", "IsHoliday", "IsBusinessDay", "Day Type", "ISO Year", "ISO CurrYearOffset", "ISO Quarter Number", "ISO Quarter", "ISO Quarter & Year", "ISO QuarternYear", "ISO CurrQuarterOffset", "Fiscal Year", "Fiscal CurrYearOffset", "Fiscal Quarter", "FQuarternYear", "Fiscal Period Number", "Fiscal Period", "FPeriodnYear", "Fiscal Week Number", "Fiscal Week", "FWeeknYear", "IsCurrentFY", "IsCurrentFQ", "IsCurrentFP", "IsCurrentFW", "IsPYTD", "IsPFYTD"}),
        ListCols = if FWlogic then Table.RemoveColumns(ReorderCols, {"ISO Quarter Number", "Fiscal Year", "Fiscal Quarter", "FQuarternYear", "Fiscal Period Number", "Fiscal Period", "FPeriodnYear", "Fiscal Week Number", "Fiscal Week", "FWeeknYear", "Fiscal CurrYearOffset", "IsCurrentFY", "IsCurrentFQ", "IsCurrentFP", "IsCurrentFW", "IsPFYTD"}) else Table.RemoveColumns(ReorderCols, {"Fiscal Period Number", "Fiscal Week Number", "ISO Quarter Number"})
    in
        ListCols
in
    Value.ReplaceType(fnDateTable, Value.ReplaceMetadata(Value.Type(fnDateTable), [
        Documentation.Name = " fxCalendar", 
        Documentation.Description = " Date table function to create an ISO-8601 calendar", 
        Documentation.LongDescription = " Date table function to create an ISO-8601 calendar", 
        Documentation.Category = " Table", 
        Documentation.Version = " 2.04: Optimized Version",
        Documentation.Source = " local", 
        Documentation.Author = " Melissa de Korte", 
        Documentation.Examples = { [Description = " See: https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390", 
            Code = " Optional paramters: #(lf)(FYStartMonthNum) Month number the fiscal year starts, Januari if omitted #(lf)(Holidays) Select a query (column) that contains a list of holiday dates #(lf)(WDStartNum) Switch default weekday numbering from 0-6 to 1-7 by entering a 1 #(lf)(AddRelativeNetWorkdays) if true adds a Relative Networkdays column to the date table #(lf)
                #(lf)Important notes: #(lf)[Fiscal Week] starts on a Monday and can contain less than 7 days in a First- and/or Last Week of a FY #(lf)[IsWeekDay] does not take holiday dates into account #(lf)[IsBusinessDay] does take optional holiday dates into account  #(lf)[IsPYTD] and [IsPFYTD] compare Previous [Day of Year] with the Current [Day of Year] number, so dates don't align in leap years #(lf)> No Fiscal columns will be added if (FYStartMonthNum) is omitted!", 
            Result = " " ] }
    ]))

C# script (TE2 and TE3 compatible) for a quick setup of this Extended Date table with Tabular Editor.

ISO 'month pattern' extension

The ISO-8601 week calendar doesn’t define months. A common requirement is to treat each quarter as 13 weeks, using a retail calendar pattern such as 4-4-5, 4-5-4, or 5-4-4.

Below is a custom function fxISOMonths that maps an ISO week number (1 - 53) to a corresponding month index according to the selected 13-week pattern. When a year includes a 53rd week, you can optionally collapse the resulting partial 13th month into month 12.

fxISOMonths (custom function)

(isoWeek as number, pattern as text, optional collapseToM12 as logical) as number =>
let
    p = Text.Select(pattern, Text.ToList("45")),
    errWK = if (isoWeek < 1 or isoWeek > 53) 
        then error "fxISOMonths: isoWeek must be an integer between 1 and 53 (inclusive)." 
        else null,
    errPAT = if not (Text.Length(p) =3 and Text.Length(Text.Select(p, "5")) =1) 
        then error "fxISOMonths: pattern must be three numbers (4, 5) that sum to 13 (e.g. 4-4-5, 4-5-4, 5-4-4)." 
        else null,
    collapse = (isoWeek = 53) and (collapseToM12 ?? false),
    q = Number.RoundUp(isoWeek /13), 
    wq = let x = isoWeek - (q - 1) * 13 in if x > 13 then 13 else x, 
    c1 = if Text.StartsWith(p, "5") then 5 else 4, 
    c2 = if Text.EndsWith(p, "5") then 8 else 9, 
    iq = if wq <= c1 then 1 else if wq <= c2 then 2 else 3, 
    isoMonth = (q - 1) * 3 + iq,
    r = if (errWK & errPAT) = null 
        then if collapse then 12 else isoMonth
        else null
in
    r 

Notes

• This function accepts flexible pattern formats (e.g. "4-4-5", "445", "4/4/5", "4 4 5").
• Ensures the pattern truly describes a 13-week quarter.
• Has the option to put week 53, when present, into Q4’s final month. month 12.

Example

The query below calls fxCalendar, the extended Date table M function, adds three columns - one for each valid pattern - and keeps a small set of columns for illustration. You can remove that RemoveOthers and other steps once you’re happy with the result.

let
    Source = fxCalendar(#date(2016, 1, 1), #date(2026, 12, 31), 7, null, 1, null),
    month445  = Table.AddColumn(Source, "ISO Month (4-4-5)", each fxISOMonths([Week Number], "4-4-5"), Int64.Type),
    month454  = Table.AddColumn(month445, "ISO Month (4-5-4)", each fxISOMonths([Week Number], "4-5-4"), Int64.Type),
    month544  = Table.AddColumn(month454, "ISO Month (5-4-4)", each fxISOMonths([Week Number], "5-4-4"), Int64.Type),
    RemoveOthers = Table.SelectColumns(month544,
        {"Date", "Year", "ISO Year", "ISO Quarter", "Week Number", "ISO Month (4-4-5)", "ISO Month (4-5-4)", "ISO Month (5-4-4)"}
    )
in
    RemoveOthers

Enjoy!