Custom Column - Previous Fiscal Year

[Unknowncharacte]

Hello, 

I am trying to identify previous FISCAL year. My fiscal year starts on October 1st and ends on September 30th, for example:

Calendar Date	Fiscal Year
09/29/2024	2024
09/30/2024	2024
10/01/2024	2025
10/02/2024	2025

I have a calendar table in which I want to add a column identifying previous FISCAL year, here are my columns

Calendar Date	Calendar Year	Fiscal Year	Month Name	Month Number
10/01/2024	2024	2025	October	10

@Anonymous helped me figure out current fiscal year in this post, looking for help identifying previous fiscal year now. My attempts to change his syntax to get previous fiscal year did not work.

https://community.fabric.microsoft.com/t5/Power-Query/Custom-Column-Current-Fiscal-Year/td-p/4035594#M132283 

[collinsg · Accepted solution]

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

[Anonymous]

Hi @Unknowncharacte ,
Thanks for @collinsg reply.
You can download the sample pbix file by clicking on the attached file under @collinsg  post.

Best regards,
Albert He

[collinsg · Accepted solution]

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

[Unknowncharacte]

Thank you so much! For some reason, I cannot get it to work, would it be possible to share a sample pbx file?

[collinsg · Accepted solution]

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