Relate Fiscal year with Calendar table

Hi Anonymous,

1.) you can enter FY and EndDate manualy by clicking on the GEAR icon next to step name FiscalDates.

2.) you have to refer your date table in step DateTable (if you don't know how, check note below my post)

(there are two dates 31.3.2024 on the picture below, but I've repaired it in the code)

let
    FiscalDates = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSAVEm+gbG+saGSrE6YFETiKipvoGRvpGFUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FY = _t, EndDate = _t]),
    FiscalDatesChangedType = Table.TransformColumnTypes(FiscalDates,{{"FY", Int64.Type}, {"EndDate", type date}}),
    FiscalDatesGenDate = [ a = Table.FromColumns(Table.ToColumns(Table.Sort(FiscalDatesChangedType, {{"EndDate", Order.Ascending}})) & {{null} & List.Transform(List.RemoveLastN(FiscalDatesChangedType[EndDate], 1), (x)=> Date.AddDays(x,1))}, Value.Type(Table.FirstN(FiscalDatesChangedType, 0) & #table(type table[StartDate=date], {}))),
    b = Table.AddColumn(a, "Date", each List.Dates([StartDate] ?? #date([FY],1,1), Duration.TotalDays([EndDate] - ([StartDate] ?? #date([FY],1,1)))+1, #duration(1,0,0,0)), type {date}),
    c = Table.ExpandListColumn(b, "Date"),
    d = Table.SelectColumns(c, {"Date", "FY"})
  ][d],
    DateTable = Table.FromList(List.Dates(#date(2023,11,1), Duration.TotalDays(#date(2024,5,31) - #date(2023,11,1))+1, #duration(1,0,0,0)), (x)=> {x}, type table[Date=date]),
    MergedQueries = Table.NestedJoin(DateTable, {"Date"}, FiscalDatesGenDate, {"Date"}, "FiscalDates", JoinKind.LeftOuter),
    ExpandedFY = Table.ExpandTableColumn(MergedQueries, "FiscalDates", {"FY"}, {"FY"})
in
    ExpandedFY