Forum Discussion
POWER QUERY custom Colum if between 2 dates answer is what ever i type in as example
Can anyone help why this keeps coming up with token litral expected error
copy and pasted
if[Posting Date]>=#date(2022,4,1)and<=#date(2023,3,1)then"FY22/23"else if[Posting Date]>=#date(2023,4,1)and<=#date(2024,3,1)then"FY23/24"else
etc etc for each financial year i add
You are missing the literal after each "and". The compiler doesn't know what that second clause in the "and" should be compared with. In addition, your algorithm doesn't account for any dates that are in the month of March other than March 1.
if[Posting Date]>=#date(2022,4,1)and [Posting Date] <=#date(2023,3,1)then"FY22/23"else if[Posting Date]>=#date(2023,4,1)and [Posting Date] <=#date(2024,3,1)then"FY23/24" else.
By the way, instead of a long "if" statement referring to each fiscal year, you could just compute it from the Posting Date.
From the Advanced Editor:
#"Added Custom" = Table.AddColumn(#"Changed Type", "Fiscal Year", each [a=Number.Mod(Date.Year([Posting Date]),100), b=Date.Month([Posting Date]), c=if b < 4 then "FY" & Number.ToText(a-1,"00/") & Number.ToText(a) else "FY" & Number.ToText(a,"00/") & Number.ToText(a+1,"00") ][c],type text)
1 Reply
- ronrsnfld
Super User
You are missing the literal after each "and". The compiler doesn't know what that second clause in the "and" should be compared with. In addition, your algorithm doesn't account for any dates that are in the month of March other than March 1.
if[Posting Date]>=#date(2022,4,1)and [Posting Date] <=#date(2023,3,1)then"FY22/23"else if[Posting Date]>=#date(2023,4,1)and [Posting Date] <=#date(2024,3,1)then"FY23/24" else.
By the way, instead of a long "if" statement referring to each fiscal year, you could just compute it from the Posting Date.
From the Advanced Editor:
#"Added Custom" = Table.AddColumn(#"Changed Type", "Fiscal Year", each [a=Number.Mod(Date.Year([Posting Date]),100), b=Date.Month([Posting Date]), c=if b < 4 then "FY" & Number.ToText(a-1,"00/") & Number.ToText(a) else "FY" & Number.ToText(a,"00/") & Number.ToText(a+1,"00") ][c],type text)