Can't Get Sort Order Correct in Pivot Table

[gatorrob86]

I created a pivot table to compare monthly totals year over year. Each data record has a date (named moyr) which is always the first of the month (so that all of my data is grouped by month). My columns are moyr and Years (which Excel inserts). My Values are Sum of amount. This produces a pivot table with 3 columns for each month (last year, this year, both years' amounts added together). In my data query I multiply last year's numbers by -1 so that when the two years' amounts are summed, I get the net difference. I know that is clunky, but I didn't know how else to do it.

So now the problem. I sort the columns from newest to oldest. This worked great until January. I should be seeing Jan 2019/2018 followed by Dec 2018/2017 followed by Nov 2018/2017 etc. But it is sorting by month only. So, I get Dec 2018/2017, Nov 2018/2017, etc and Jan 2019/2018 is last. Any help would be greatly appreciated. And I am totally open to reworking my data query if that's what is needed.

Here is a screen shot of my pivot table. I hide the middle months so you can see both ends of the sort order.

PivotTable.png

[Richard Buttrey]

Hi and welcome to the forum

Are your dates proper date numbers?

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[rorya]

It sounds like you need to not group the dates. Instead add a new calculated year column to your data source, then use the original date column as the first column field and format it as month only, with the new year field as the second column field.