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
Hi Henry,
In Power Pivot you can set a 'Sort By' column rule in the modelling. This will force a PivotTable to sort any column by another numeric column. You simply add another column that numbers the fields in the order you want, then go to the modelling tab and set the 'Sort By'.
Hope that points you in the right direction.
Mynda
Thank you Mynda, that would do it. On this occasion, a custom list was my best solution.
Henry
