Hi @Vishy,
Please see how you get on with this date table M query.
It requires 2 parameter, a StartDate for your first FY, in your case = 3 jan 2021 and how many years you want to include in your date table.
let fx544DateTable = (StartDate as date, NumOfYears as number) as table =>
let
EndDate = Date.AddDays( StartDate, (NumOfYears * 364 ) -1 ),
Source = Table.FromColumns( {{ Number.From(StartDate)..Number.From(EndDate) }} ),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "DayID", 1, 1, Int64.Type),
AddFY = Table.AddColumn(#"Added Index", "FY ID", each Number.RoundDown(([DayID]-1)/364)+1, type number),
AddFQ = Table.AddColumn(AddFY, "FQ ID", each Number.RoundDown(([DayID]-1)/91)+1, type number),
AddFP = Table.AddColumn(AddFQ, "FP ID", each Number.RoundDown([DayID]/91) * 3 +
( if Number.Mod([DayID],91)=0 then 0
else if Number.Mod([DayID],91)<=35 then 1
else if Number.Mod([DayID],91)<=63 then 2
else 3
),
type number ),
AddFW = Table.AddColumn(AddFP, "FW ID", each Number.RoundDown(([DayID]-1)/7)+1, type number),
FiscalYear = Table.AddColumn(AddFW, "FY", each Date.Year( StartDate ) + [FY ID] -1, type number),
FiscalQuarter = Table.AddColumn(FiscalYear, "FQ", each [FQ ID]-(([FY ID]-1)*4), type number),
FiscalPeriod = Table.AddColumn(FiscalQuarter, "FP", each [FP ID] - (([FY ID]-1) * 12 ), type number),
FiscalWeek = Table.AddColumn(FiscalPeriod, "FW", each [FW ID] - (([FY ID]-1) * 52 ), type number),
QinFY = Table.AddColumn(FiscalWeek, "Quarter in FY", each Text.From([FY]) & " - Q" & Text.From([FQ]), type text),
PinFY = Table.AddColumn(QinFY, "Period in FY", each Text.From([FY]) & " - P" & Text.PadStart( Text.From([FP]), 2, "0"), type text),
WinFY = Table.AddColumn(PinFY, "Week in FY", each Text.From([FY]) & " - W" & Text.PadStart( Text.From([FW]), 2, "0"), type text)
in
WinFY
in fx544DateTable
You can extent the code however you see fit. This article forms the basis of this 544 date table M code.
I hope this is helpful.
