Custom Column - Previous Fiscal Year

Good day Unknowncharacte ,

I've written a custom function, fnOffsetFY, to calculate the FY offset by "offset" years for date "dt" where the FY starts on month "fym" and day "fyd" e.g. to calculate the previous FY for #date(2023,10,6)

= fnOffsetFY(#date(2023,10,6), 10, 1, -1)

 Here is the function (if you have dates before 2000 change the 2000 to be earlier than your earliest date).

(dt as any, fym as number, fyd as number, offset as number) as any =>
    let
        yyyy = Date.Year(dt),
        m = Date.Month(dt),
        d = Date.Day(dt),
        result = if #date(2000, m, d) >= #date(2000, fym, fyd) then yyyy + offset + 1 else yyyy + offset
    in
        result

...and here is an example of its application

let
Source = List.Generate( () => [i=1, d=#date(2022,1,1)], each [i] <= 24, each [i=[i]+1, d=Date.AddMonths([d],1)], each [d] ),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), type table [Calendar Date=date], null, ExtraValues.Error),
#"Add current FY" = Table.AddColumn(#"Converted to Table", "Current FY", each fnOffsetFY([Calendar Date], 10, 1, 0), type number),
#"Add previous FY" = Table.AddColumn(#"Add current FY", "Previous FY", each fnOffsetFY([Calendar Date], 10, 1, -1), type number),
#"Add next FY" = Table.AddColumn(#"Add previous FY", "Next FY", each fnOffsetFY([Calendar Date], 10, 1, 1), type number)
in
#"Add next FY"

...which gives this result (my dates are UK format but the function will work with US locale).

I've attached an example Excel workbook. 

Hope this helps

Good day Unknowncharacte ,

Here's the example in a .pbix. If you have dates earlier than 2000 then change the "2000"s in this line to before your earliest date

result = if #date(2000, m, d) >= #date(2000, fym, fyd) then yyyy + offset + 1 else yyyy + offset

e.g.

result = if #date(1920, m, d) >= #date(1920, fym, fyd) then yyyy + offset + 1 else yyyy + offset

Hope this helps