Sorting by Financial Year and then month within that year|Power Query|Excel Forum|My Online Training Hub

This topic covers both PQ & Pivot Tables but I think it best here!

I have created a column (say FY) for the financial year (May to Apr) of some data using

Date.Year([Date]) + Number.From(Date.Month([Date])>=5)

which is then used for the columns in a pivot table. All good so far.

I then add a column for the month

Date.Month([Date])

which I added under FY in the pivot table, but of course the pivot table then sorts the months from Jan-Dec rather than May-April as I would like. I set up a custom list but couldn't figure out how to use that in a pivot table.

So back to PQ where I would like to subtract 3 if the month number is >3 and add 9 if <=3

presumably using if() and I've tried all sorts of variations on

if (Date.Month([Date])>3, Date.Month([Date])-3, Date.Month([Date])+9)

but I keep getting a syntax error

I managed to get the Custom List to work, but I would still like to know where I am going wrong in PQ please

Henry