Pivot chart - prevent custom sort from resetting when data is refreshed

[ToniGo]

I have set up a data model with the main source of data being a bunch of CSV files in a particular folder.
Data is grouped by month via the pivot field which works fine.
The only issue is that I need to order the months July-June and every time the data is refreshed they divert back.

When I click on the axis and select more sort options I can see the "More Options" box but it is greyed out.

Hoping there is a simple answer! Otherwise the best idea I have to set up a macro to manually resort if the data is refreshed.

 

[oldbrewer]

do you have 12 months with a single data value by each ? if the pivot after refreshing is in the order in in say order jan to dec and you want dec back to jan a simple new table pulling the months in you specified order could prepare the data for the chart.....

 

[oldbrewer]

month	score
jan	1							assuming you want dec to jan
feb	2
mar	3		Sum of score					dec	36
apr	4		month	Total				nov	34
may	5		jan	14				oct	32
jun	6		feb	16				sep	30
july	7		mar	18				aug	28
aug	8		apr	20				july	26
sep	9		may	22				jun	24
oct	10		jun	24				may	22
nov	11		july	26				apr	20
dec	12		aug	28				mar	18
jan	13		sep	30				feb	16
feb	14		oct	32				jan	14
mar	15		nov	34
apr	16		dec	36
may	17		Grand Total	300
jun	18							formula giving 36 for dec is
july	19
aug	20							=OFFSET($D$5,MATCH(I4,$D$6:$D$17,0),1)
sep	21
oct	22
nov	23
dec	24

<colgroup><col span="3"><col><col><col span="10"></colgroup><tbody>
</tbody>

 

[ToniGo]

No, I have multiple years with a single entry for each month in each year. So I have it set up as a standalone pivot chart and would prefer not to link it to formula table but that is another option. Ideally I would just like to lock the order in so it stays as I have set it and not change the order once refreshed.

 

[mart37]

2 options:
Add a custom list with your sort order
Add a calculated field with = month (Table1 [datefield]) and sort on that field

 

[ToniGo]

2 options:
Add a custom list with your sort order
Add a calculated field with = month (Table1 [datefield]) and sort on that field

That sounds perfect thanks, but I can't see how to sort based on a different list?

 

[ToniGo]

So hopefully this might help someone else. I just added a new pivot table in the same tab with a list of the months and was able to change the sort settings on that to not re-sort automatically. Since then my pivot charts have also not re-sorted (even though I didn't connect them). So problem solved.