Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
eagertolearn1
New Member

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

 

eagertolearn1_1-1750852072686.png

 

 

1 ACCEPTED SOLUTION
ronrsnfld
Super User
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)

View solution in original post

1 REPLY 1
ronrsnfld
Super User
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)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.