IF statement for financial year

M

Martin69

New member
Joined
Aug 11, 2023
Messages
8
Reaction score
0
Points
1
Excel Version(s)
365
Hi clever people.

I'm creating a calendar table in Power Query and I'm trying to create a financial year column from the date. So if the date is <= 4/4/YYYY then it should show this year, if it's > 4/4/YYYY, it should return next year.

3/4/20212021
4/4/20212021
5/4/20212022
6/4/20212022
etc.

Here's my code.

M: 
= Table.AddColumn(#"Inserted Year", "FY", each 
                          if 
                                Date.Month([Date]) <= 4 and Date.Day([Date]) <= 4 
                          then 
                                Date.Year([Date]) 
                          else
                                Date.Year([Date]) + 1
)

This is wrong somewhere as in the example above, the first 4 days of EVERY month are output as 2021 and the 5th to the "31st" are output as 2022. Seems fairly simple, but I'm having a stupid moment I think.

Anyone able to tell me what I've done wrong?

Thanks
 
Try something along the lines of:
Code: 
Table.AddColumn(PreviousStep, "FY", each Date.Year([Date]) + (if [Date]>= #date(Date.Year([Date]),4,5) then 1 else 0))
Experiment playing with the day of the month in #date() and the >= maybe being just > to get the exact date of the financial year switchover as you want it.
 
Last edited:
Thank you very much. I'm out at the moment but will try it as soon as I get home.

Just one question though. What was actually wrong with my original code? I can't see it.
 
in:
Date.Month([Date]) <= 4 and Date.Day([Date]) <= 4

the Date.Month([Date]) <= 4 part is true for Jan-Apr

the Date.Day([Date]) <= 4 part is only true in the 1st 4 days of those months.
 
Gotcha. Told you I was having a stupid moment. A nested If would have solved it.
 
Will do once I get back in front of my PC.
 
My blatherings around nesting came to nothing. Your solution however is simple and perfect. You did not one, but two very clever things.
  1. #date(Date.Year([Date]),4,5)
  2. Adding 1 to the output.
You've given me a lot to think about :)
 
This was the final version:

M: 
let
    Cal = Table.FromList(List.Dates( #date(2021, 3, 29),  Number.From(DateTime.Date(DateTime.LocalNow())- #date(2021, 3, 29)) + 1, #duration(1, 0, 0, 0)), Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    ChangedType = Table.TransformColumnTypes(Cal,{{"Date", type date}}),
    FinancialYear = Table.AddColumn(ChangedType, "FY", 
        each Date.Year([Date]) + (
            if 
                [Date]>= #date(Date.Year([Date]),4,5) 
            then 
                1 
            else 
                0)),
    YearEnd = Table.AddColumn(FinancialYear, "YearEnd", each Text.Replace(Text.From([FY], "en-GB"), "20", "04/04/"), type text),
    RemovedColumns = Table.RemoveColumns(YearEnd,{"FY"})
in
    RemovedColumns
 
…and then you remove that column!
Why not do it all in one line? It remains a proper date type too:
Code: 
let
    Cal = Table.FromList(List.Dates( #date(2021, 3, 29),  Number.From(DateTime.Date(DateTime.LocalNow())- #date(2021, 3, 29)) + 1, #duration(1, 0, 0, 0)), Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    ChangedType = Table.TransformColumnTypes(Cal,{{"Date", type date}}),
    FinancialYear = Table.AddColumn(ChangedType, "YearEnd",
        each #date(Date.Year([Date]) + (
            if
                [Date]> #date(Date.Year([Date]),4,4)
            then
                1
            else
                0),4,4), Date.Type )
in
    FinancialYear
 
Once again, your code is considerably better than mine :)
 
You must log in or register to reply here.
Back
Top