Create a custom column and assign financial year

hello, 

    fy_end = {#date(2023, 03, 31), #date(2024, 03, 31), #date(2025, 03, 31)},
    fy = {"FY22", "FY23", "FY24"},
    fy_column = Table.AddColumn(
        your_table, 
        "FY", 
        (x) => fy{List.PositionOf(fy_end, x[Expense_Date], Occurrence.First, (x, y) => y <= x)}
    )

Hi Jaslyn 

Thanks for the solution AlienSx  and wdx223_Daniel  provided, the solution wdx223_Daniel  provided, its previous step means your last step name, e.g #"Changed Type".

And i offer some more information for you to refer to.

You can create a custom column and input the following code.

 

let a=#date(Date.Year([Expense_date]),4,1),
b=#date(Date.Year([Expense_date])+1,3,31)
in if [Expense_date]>=a and [Expense_date]<=b then "FY"&Text.End(Number.ToText(Date.Year([Expense_date])),2) else "FY"&Text.End(Number.ToText(Date.Year([Expense_date])-1),2)

 

Output

The following is the whole M code 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fcs7CoBAEAPQu0ztkJ3M4qf0HMs2wmoriPdXhMFG7F5CUoow6XxuSkrtipjr0XalP4mua1siWcaEMAbEBcYw0QcdZq/Hr9qQ/0nc28QstV4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Expense_date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Expense_date", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Expense_date", type date}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each let a=#date(Date.Year([Expense_date]),4,1),
b=#date(Date.Year([Expense_date])+1,3,31)
in if [Expense_date]>=a and [Expense_date]<=b then "FY"&Text.End(Number.ToText(Date.Year([Expense_date])),2) else "FY"&Text.End(Number.ToText(Date.Year([Expense_date])-1),2))
in
    #"Added Custom"

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.