custom sort pivot

[ajm]

is it possible to have a pivot table sorted by a custom list that is created on refresh of the pivot table? I am working with a rolling 12 months of results from this year and comparing it to the same month from 1 year ago. i want to have the latest month always in front. I have a custom list set up for the Australian financial year (July to June) which works this month. Next month, when i want to see August results first (top row in my pivot table and last position on my graph), July will still be listed first because of my custom list.

so, how do i make that list order change each month?

 

[GraH]

Hi, a workaround can be to have an extra column in your data on which you can do the sort order as in use this dimension in your pivot.
=IF(MONTH(varDate)<=MONTH(TODAY()),MONTH(TODAY())-MONTH(varDate)+1,MONTH(TODAY())+(MONTH(varDate)-MONTH(TODAY()))) & " - " & TEXT (varDate,"mmm")

Downside it uses many times today()... volatile.

 

[ajm]

thanks GraH. could workaround the multiple todays with a reference to a particular cell holding the Today() perhaps. So, put Today() in (for example) G# then your formula would read
=IF(MONTH(varDate)<=MONTH($G$3),MONTH($G$3)-MONTH(varDate)+1,MONTH($G$3)+(MONTH(varDate)-MONTH($G$3))) & " - " & TEXT (varDate,"mmm")

 

[GraH]

For sure that works. You might even consider populating that cell with a simple macro or even with CTRL+ : if that shortcut for today is generic. Since in your case the date must only be updated once a month.