Hi @Jose,
This took a bit of work because of your custom 445 weekday logic, doesn’t follow a pattern I could identify… Paste this into a new blank query, invoke this custom date table function, set the StartOfWeekDay =6 and let me know how you get on with it…
let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonthNum as number, optional Holidays as list, optional StartOfWeekDay as number, optional WDStartNum as number ) as table =>
let
FYStartMonth = if List.Contains( {1..12}, FYStartMonthNum ) then FYStartMonthNum else 1,
StartOfWeekDayNum = if List.Contains( {0..6}, StartOfWeekDay ) then StartOfWeekDay else 1,
WDStart = if List.Contains( {0, 1}, WDStartNum ) then WDStartNum else 0,
CurrentDate = Date.From(DateTime.FixedLocalNow()),
DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
AddToday = if EndDate < CurrentDate then List.Combine( {Source, {CurrentDate}}) else Source,
TableFromList = Table.FromList(AddToday, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number),
InsertYearOffset = Table.AddColumn(InsertYear, "YearOffset", each Date.Year([Date]) - Date.Year(Date.From(CurrentDate)), type number),
InsertCompletedYear = Table.AddColumn(InsertYearOffset, "YearCompleted", each Date.EndOfYear([Date]) < Date.From(Date.EndOfYear(CurrentDate)), type logical),
InsertQuarter = Table.AddColumn(InsertCompletedYear, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number),
InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter & Year", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text),
InsertQuarternYear = Table.AddColumn(InsertCalendarQtr, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100, type number),
InsertQuarterOffset = Table.AddColumn(InsertQuarternYear, "QuarterOffset", each ((4 * Date.Year([Date])) + Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) + Date.QuarterOfYear(Date.From(CurrentDate))), type number),
InsertCompletedQuarter = Table.AddColumn(InsertQuarterOffset, "QuarterCompleted", each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), type logical),
InsertMonth = Table.AddColumn(InsertCompletedQuarter, "MonthOfYear", each Date.Month([Date]), type number),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number),
InsertMonthName = Table.AddColumn(InsertDay, "Month Name", each Text.Proper( Date.ToText([Date], "MMMM")), type text),
InsertMonthShort = Table.AddColumn( InsertMonthName, "MonthShortName", each try Text.Proper( Text.Start([Month Name], 3 )) otherwise Text.Proper( [Month Name] ), type text),
InsertMonthInitial = Table.AddColumn(InsertMonthShort, "Month Initial", each Text.Proper(Text.Start([Month Name], 1)) & Text.Repeat( Character.FromNumber(8203), [MonthOfYear] ), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthInitial, "Month & Year", each [MonthShortName] & " " & Number.ToText([Year]), type text),
InsertMonthnYear = Table.AddColumn(InsertCalendarMonth , "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),
InsertMonthOffset = Table.AddColumn(InsertMonthnYear, "MonthOffset", each ((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) + Date.Month(Date.From(CurrentDate))), type number),
InsertCompletedMonth = Table.AddColumn(InsertMonthOffset, "MonthCompleted", each Date.EndOfMonth([Date]) < Date.From(Date.EndOfMonth(CurrentDate)), type logical),
InsertMonthEnding = Table.AddColumn(InsertCompletedMonth, "MonthEnding", each Date.EndOfMonth([Date]), type date),
InsertDayInt = Table.AddColumn(InsertMonthEnding, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number),
InsertDayOfYear = Table.AddColumn(InsertDayInt, "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
InsertDayWeek = Table.AddColumn(InsertDayOfYear, "DayOfWeek", each Date.DayOfWeek([Date], StartOfWeekDayNum ) + WDStart, Int64.Type),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Text.Proper( Date.ToText([Date], "dddd" )), type text),
InsertDayInitial = Table.AddColumn(InsertDayName, "Weekday Initial", each Text.Proper(Text.Start([DayOfWeekName], 1)) & Text.Repeat( Character.FromNumber(8203), [DayOfWeek] ), type text),
InsertWeekStart = Table.AddColumn(InsertDayInitial, "WeekStarting", each Date.StartOfWeek( [Date], StartOfWeekDayNum), type date),
InsertWeekOffset = Table.AddColumn(InsertWeekStart, "WeekOffset", each (Number.From(Date.StartOfWeek([Date], StartOfWeekDayNum))-Number.From(Date.StartOfWeek(CurrentDate, StartOfWeekDayNum)))/7, type number),
InsertCompletedWeek = Table.AddColumn(InsertWeekOffset, "WeekCompleted", each Date.EndOfWeek( [Date], StartOfWeekDayNum) < Date.From(Date.EndOfWeek(CurrentDate, StartOfWeekDayNum)), type logical),
InsertWeekEnding = Table.AddColumn(InsertCompletedWeek, "WeekEnding", each Date.EndOfWeek( [Date], StartOfWeekDayNum), type date),
AddFY = Table.AddColumn(InsertWeekEnding, "Fiscal Year", each "FY" & (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then Text.PadEnd( Text.End( Text.From([Year] +1), 2), 2, "0") else Text.End( Text.From([Year]), 2)), type text),
AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", each "FQ" & Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 )), type text),
AddFQnYr = Table.AddColumn(AddFQ, "FQuarternYear", each (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 10000 + Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 ) * 100, type number),
AddFM = Table.AddColumn(AddFQnYr, "Fiscal Period", each if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [MonthOfYear] - (FYStartMonth-1) else if [MonthOfYear] >= FYStartMonth and FYStartMonth =1 then [MonthOfYear] else [MonthOfYear] + (12-FYStartMonth+1), type text),
AddFMnYr = Table.AddColumn(AddFM , "FPeriodnYear", each (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 10000 + [Fiscal Period] * 100, type number),
FYCalendarStart = #date( Date.Year(StartDate)-1, FYStartMonth, 1 ),
InsertFFD = Table.AddColumn( AddFMnYr, "FiscalFirstDay", each if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then #date( Date.Year([Date])+1, FYStartMonth, 1) else #date( Date.Year([Date]), FYStartMonth, 1) ),
AddFYDateRange = Table.Buffer( Table.ExpandTableColumn( Table.ExpandTableColumn( Table.AddColumn( Table.Group( Table.Group( Table.AddColumn( Table.AddColumn(
Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( { Number.From(FYCalendarStart)..Number.From(EndDate) }, Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "Date"}}),
"FiscalFirstDay", each if Date.Month([Date]) < FYStartMonth then #date( Date.Year([Date]), FYStartMonth, 1) else #date( Date.Year([Date])+1, FYStartMonth, 1)),
"FWStartDate", each Date.AddYears(Date.StartOfWeek( [Date], StartOfWeekDayNum), 1)),
{"FiscalFirstDay", "FWStartDate"}, {{"AllRows", each _, type table [Date=nullable date, FiscalFirstDay=date, FWStartDate=date]}}),
{"FiscalFirstDay"}, {{"AllRows2", each _, type table [FiscalFirstDay=date, FWStartDate=date, AllRows=table]}}),
"Custom", each Table.AddIndexColumn( [AllRows2], "FY Week", 1, 1))[[Custom]],
"Custom", {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}, {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}),
"AllRows", {"Date"}, {"Date"})[[Date], [FY Week]]
),
MergeFYW = Table.NestedJoin(InsertFFD, {"Date"}, AddFYDateRange, {"Date"}, "AddFYWeek", JoinKind.LeftOuter),
ExpandFYWeek = Table.TransformColumnTypes( Table.ExpandTableColumn(MergeFYW, "AddFYWeek", {"FY Week"}, {"Fiscal Week"}),{{"Fiscal Week", Int64.Type}}),
AddFYW = Table.AddColumn( ExpandFYWeek, "Fiscal Year & Week", each if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then Text.From( Date.Year([Date])+1) & "-" & Text.PadStart(Text.From([Fiscal Week]), 2, "0") else Text.From( Date.Year([Date])) & "-" & Text.PadStart( Text.From([Fiscal Week]), 2, "0"), type text),
InsertFWeeknYear = Table.AddColumn(AddFYW, "FWeeknYear", each (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then Date.Year([Date])+1 else Date.Year([Date])) * 10000 + [Fiscal Week] * 100, Int64.Type),
InsertIsAfterToday = Table.AddColumn(InsertFWeeknYear, "IsAfterToday", each not ([Date] <= Date.From(CurrentDate)), type logical),
InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if Date.DayOfWeek([Date], Day.Monday) > 4 then false else true, type logical),
InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [Date] ), if Holidays = null then type text else type logical),
InsertIsBusinessDay = Table.AddColumn(InsertIsHoliday, "IsBusinessDay", each if [IsWorkingDay] = true and [IsHoliday] <> true then true else false, type logical),
InsertDayType = Table.AddColumn(InsertIsBusinessDay, "Day Type", each if [IsHoliday] = true then "Holiday" else if [IsWorkingDay] = false then "Weekend" else if [IsWorkingDay] = true then "Weekday" else null, type text),
CurrentDateRecord = Table.SelectRows(InsertDayType, each ([Date] = CurrentDate)),
CurrentISOyear = CurrentDateRecord{0}[ISO Year],
CurrentISOqtr = CurrentDateRecord{0}[ISO QuarterOfYear],
CurrentYear = CurrentDateRecord{0}[Year],
CurrentMonth = CurrentDateRecord{0}[MonthOfYear],
CurrentFiscalFirstDay = CurrentDateRecord{0}[FiscalFirstDay],
PrevFiscalFirstDay = Date.AddYears(CurrentFiscalFirstDay, -1),
CurrentFQ = CurrentDateRecord{0}[FQuarternYear],
CurrentFP = CurrentDateRecord{0}[FPeriodnYear],
CurrentFW = CurrentDateRecord{0}[FWeeknYear],
InsertFYoffset = Table.AddColumn(InsertDayType, "FiscalYearOffset", each try (if [MonthOfYear] >= FYStartMonth then [Year]+1 else [Year]) - (if CurrentMonth >= FYStartMonth then CurrentYear+1 else CurrentYear) otherwise null, type number),
InsertCurrentFQ = Table.AddColumn(InsertFYoffset, "IsCurrentFQ", each if [FQuarternYear] = CurrentFQ then true else false, type logical),
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),
ListPrevFYDates = List.Buffer( Table.SelectRows( Table.ExpandTableColumn( Table.NestedJoin(
Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( PrevFiscalFirstDay, Number.From(CurrentFiscalFirstDay-PrevFiscalFirstDay),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1), {"Index"},
Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( Date.AddYears( PrevFiscalFirstDay, -1), Number.From( PrevFiscalFirstDay - Date.AddYears( PrevFiscalFirstDay, -1)),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1)
, {"Index"}, "Table", JoinKind.LeftOuter), "Table", {"DateFY"}, {"PrevDateFY"}), each [DateFY] <= CurrentDate)[PrevDateFY] ),
InsertPFYTD = Table.AddColumn(InsertPYTD, "IsPFYTD", each if [FiscalYearOffset] = -1 and List.Contains(ListPrevFYDates, [Date] ) then true else false, type logical),
RemoveToday = Table.RemoveColumns( if EndDate < CurrentDate then Table.SelectRows(InsertPFYTD, each ([Date] <> CurrentDate)) else InsertPFYTD, {"Day of Year", "FiscalFirstDay"}),
// Custom 445 weekday logic for details see: https://forum.enterprisedna.co/t/workday-numbers-within-445-calendar/19283
GroupYears = Table.Group(RemoveToday, {"Year"}, {{"AllRows", each _, type table [Date=nullable date, Year=number, YearOffset=number, YearCompleted=logical, QuarterOfYear=number, #"Quarter & Year"=text, QuarternYear=number, QuarterOffset=number, QuarterCompleted=logical, MonthOfYear=number, DayOfMonth=number, Month Name=text, MonthShortName=text, Month Initial=text, #"Month & Year"=text, MonthnYear=number, MonthOffset=number, MonthCompleted=logical, MonthEnding=date, DateInt=number, DayOfWeek=number, DayOfWeekName=text, Weekday Initial=text, WeekStarting=date, WeekOffset=number, WeekCompleted=logical, WeekEnding=date, Fiscal Year=text, Fiscal Quarter=text, FQuarternYear=number, Fiscal Period=text, FPeriodnYear=number, Fiscal Week=nullable number, #"Fiscal Year & Week"=text, FWeeknYear=number, IsAfterToday=logical, IsWorkingDay=logical, IsHoliday=text, IsBusinessDay=logical, Day Type=text, FiscalYearOffset=number, IsCurrentFQ=logical, IsCurrentFP=logical, IsCurrentFW=logical, IsPYTD=logical, IsPFYTD=logical]}}),
AddTemp = Table.AddColumn(GroupYears, "Temp", each Table.RemoveColumns( Table.AddColumn( Table.AddIndexColumn( [AllRows], "Int", 1, 1), "DayID", (IT)=> if IT[Int] > 364 then 364 else IT[Int] ), "Int")),
AddCustom = Table.ExpandTableColumn( Table.AddColumn(AddTemp, "Temp2", each let myTable = [Temp][[Date],[Year],[DayOfWeek],[IsWorkingDay]], StartOfYearWeekDay = myTable{0}[DayOfWeek], GroupBy = Table.Group(myTable, {"DayOfWeek"}, {{"EachDayOfWeek", each _, type table [Date=nullable date, Year=number, DayOfWeek=number, IsWorkingDay=logical]}}),
SelectRow = Table.SelectRows(GroupBy, each if StartOfYearWeekDay =0 then ([DayOfWeek] = 0) else ([DayOfWeek] = 6)),
AddCustom = Table.RemoveColumns( Table.AddColumn(SelectRow, "Custom", each let myTable2 = [EachDayOfWeek], AddIndex = Table.AddIndexColumn(myTable2, "Index", 1, 1, Int64.Type),
Add445 = Table.AddColumn(AddIndex, "445", each if [Date] >= #date( [Year], 12, 20) then 12 else Number.RoundDown([Index]/13) * 3 + ( if Number.Mod([Index],13) =0 then 0 else if Number.Mod([Index],13) <=4 then 1 else if Number.Mod([Index],13) <=8 then 2 else 3 )), UpdateDate = if [DayOfWeek] =0 then Table.ReplaceValue(Add445, each [Date], each Date.AddDays([Date], -1),Replacer.ReplaceValue,{"Date"}) else Add445 in Table.RemoveColumns( UpdateDate, "Index" )), "EachDayOfWeek" ) in AddCustom), "Temp2", {"Custom"}, {"Custom"}),
AddTemp2 = Table.AddColumn(AddCustom, "Temp2", each Table.RemoveColumns( Table.AddColumn( Table.FillUp( Table.Sort( Table.ExpandTableColumn( Table.NestedJoin([Temp], {"Date"}, [Custom], {"Date"}, "Join", JoinKind.LeftOuter), "Join", {"445"}, {"445"}), {{"Date", Order.Ascending}}), {"445"}), "C445", each if Date.Month([Date]) = 12 then 12 else [445]), "445" ) ),
AddNew = Table.AddColumn( AddTemp2, "New", (OT) => Table.AddColumn( OT[Temp2], "WorkDayNum", each let firstVal = Date.DayOfWeek( #date( [Year], 1, 1 ), Day.Monday ), x = try Table.RowCount( Table.SelectRows( OT[Temp2], (IT) => IT[IsWorkingDay] = true and [C445] = IT[C445] and IT[Date] <= [Date] )) otherwise 0 in if [C445] = 1 and Date.DayOfWeek( #date( [Year], 1, 1 ), Day.Monday ) <5 then firstVal +x else if [C445] = 1 and Date.DayOfWeek( #date( [Year], 1, 1 ), Day.Monday ) >=5 then 0 +x else if [C445] >1 then 0 +x else 1 +x ))[[Year], [New]],
ExpandNew = Table.ExpandTableColumn(AddNew, "New", {"Date", "YearOffset", "YearCompleted", "QuarterOfYear", "Quarter & Year", "QuarternYear", "QuarterOffset", "QuarterCompleted", "MonthOfYear", "DayOfMonth", "Month Name", "MonthShortName", "Month Initial", "Month & Year", "MonthnYear", "MonthOffset", "MonthCompleted", "MonthEnding", "DateInt", "DayOfWeek", "DayOfWeekName", "Weekday Initial", "WeekStarting", "WeekOffset", "WeekCompleted", "WeekEnding", "Fiscal Year", "Fiscal Quarter", "FQuarternYear", "Fiscal Period", "FPeriodnYear", "Fiscal Week", "Fiscal Year & Week", "FWeeknYear", "IsAfterToday", "IsWorkingDay", "IsHoliday", "IsBusinessDay", "Day Type", "FiscalYearOffset", "IsCurrentFQ", "IsCurrentFP", "IsCurrentFW", "IsPYTD", "IsPFYTD", "WorkDayNum"}, {"Date", "YearOffset", "YearCompleted", "QuarterOfYear", "Quarter & Year", "QuarternYear", "QuarterOffset", "QuarterCompleted", "MonthOfYear", "DayOfMonth", "Month Name", "MonthShortName", "Month Initial", "Month & Year", "MonthnYear", "MonthOffset", "MonthCompleted", "MonthEnding", "DateInt", "DayOfWeek", "DayOfWeekName", "Weekday Initial", "WeekStarting", "WeekOffset", "WeekCompleted", "WeekEnding", "Fiscal Year", "Fiscal Quarter", "FQuarternYear", "Fiscal Period", "FPeriodnYear", "Fiscal Week", "Fiscal Year & Week", "FWeeknYear", "IsAfterToday", "IsWorkingDay", "IsHoliday", "IsBusinessDay", "Day Type", "FiscalYearOffset", "IsCurrentFQ", "IsCurrentFP", "IsCurrentFW", "IsPYTD", "IsPFYTD", "WorkDayNum"}),
ChType = Table.TransformColumnTypes(ExpandNew,{{"Year", Int64.Type}, {"Date", type date}, {"YearOffset", Int64.Type}, {"YearCompleted", type logical}, {"QuarterOfYear", Int64.Type}, {"Quarter & Year", type text}, {"QuarternYear", Int64.Type}, {"QuarterOffset", Int64.Type}, {"QuarterCompleted", type logical}, {"MonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"Month Name", type text}, {"MonthShortName", type text}, {"Month Initial", type text}, {"Month & Year", type text}, {"MonthnYear", Int64.Type}, {"MonthOffset", Int64.Type}, {"MonthCompleted", type logical}, {"MonthEnding", type date}, {"DateInt", Int64.Type}, {"DayOfWeek", Int64.Type}, {"DayOfWeekName", type text}, {"Weekday Initial", type text}, {"WeekStarting", type date}, {"WeekOffset", Int64.Type}, {"WeekCompleted", type logical}, {"WeekEnding", type date}, {"Fiscal Year", type text}, {"Fiscal Quarter", type text}, {"FQuarternYear", Int64.Type}, {"Fiscal Period", Int64.Type}, {"FPeriodnYear", Int64.Type}, {"Fiscal Week", Int64.Type}, {"Fiscal Year & Week", type text}, {"FWeeknYear", Int64.Type}, {"IsAfterToday", type logical}, {"IsWorkingDay", type logical}, {"IsBusinessDay", type logical}, {"Day Type", type text}, {"FiscalYearOffset", Int64.Type}, {"IsCurrentFQ", type logical}, {"IsCurrentFP", type logical}, {"IsCurrentFW", type logical}, {"IsPYTD", type logical}, {"IsPFYTD", type logical}, {"WorkDayNum", Int64.Type}})
in
ChType
in
fnDateTable
.
I hope this is helpful
