Forum Discussion

LyonsBI_BRL's avatar
LyonsBI_BRL
Helper III
4 years ago
Solved

PowerQuery - Registration Year using M

I'm looking to figure this out on how to set it so if the registrations dates are between May 3rd, 2021 and May 3rd, 2022 its FY22. Anything after May 4th, 2022 and beyond is FY23

 

Table.AddColumn(#"Changed Type2", "Registration Year", each if [FirstNewDate] >= #datetime(2021, 5, 3, 0, 0, 0) then "FY22" else if [FirstNewDate] < #datetime(2022, 5, 3, 0, 0, 0) then "FY22" else if [FirstNewDate] >= #datetime(2022, 5, 4, 0, 0, 0) then "FY23" else "FY23"),

 

Instead of it ouputting for any dates past May 4th, 2022 as FY23 it's listing them as FY22. Any suggestions as to why this is happening?

 

Thanks! 

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Use this

    = Table.AddColumn(#"Changed Type", "Registration Year", each if Date.From([FirstNewDate])>=#date(2021,5,3) and Date.From([FirstNewDate])<=#date(2022,5,3) then "FY22" else "FY23")

     

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Use this

    = Table.AddColumn(#"Changed Type", "Registration Year", each if Date.From([FirstNewDate])>=#date(2021,5,3) and Date.From([FirstNewDate])<=#date(2022,5,3) then "FY22" else "FY23")