Odd pivot table behavior

[MSC]

Hi, I'm fairly new to pivot tables but something strange is happening.

I can't share the datafiles as they contain sensitive information, but:
-I'm going into an online database to pull data.
-This gets dumped by the system into an xlsx file
-I have 6 time periods. In the database, 5 are named "Calendar year ending 2017" (and 2018, 2019, etc.). But one is named "Building calendar year 2022" as this year obviously hasn't finished yet. I can't change the names inside the database itself, so it appears as "Building...." in the source file that gets exported.
-When I create a pivot table from the source file, and put Time as a column label, it alphabetizes the 6 time periods, with "Building Calendar 2022" coming first, followed by Calendar 2017, Calendar 2018, etc. So, out of order.

How do I change this order?
-I'd love to directly change the order inside the pivot but can't determine how to do this.
-I've tried renaming that time period in the source data, and then Refreshing in the Pivot so it will alphabetize with the newly named Calendar 2022 correctly coming last, but Refresh does not pull in the modified name. [I -can- change a datapoint and it will refresh with the new datapoint, so Refresh appears to be working correctly.)
-I've taken the tab with the source data in it and that time period renamed, then copied it into a new file, created a pivot, but somehow the 'Building' still carries over.


And: I'm also trying to determine how to create a formula inside the pivot that subtracts one year from another. In my limited knowledge I know how to subtract one -field- from another, but not different elements inside the same field.

Thank you in advance!
-Mike

 

[SpillerBD]

You can use a custom list and set the sort order.
You could use Power Query to add a new field that corrects the data while retaining the original. (Retaining the original data wouldn't be necessary but can help answer questions from others.)

 

[MSC]

Thank you, I saw your reply a couple days ago - swamped at work on a different assignment but will try yours out.

Still mystified why the 'original' name still holds even after editing it and even dumping it into a new file and triggering the pivot from there. Feels like I should be able to monkey with the formatting of the file, or the cell itself, to allow the pivot to recognize the modification.

 

[SpillerBD]

One problem you may experience is that the "Date" is still some Text form, and therefor remains sorting as text. Some convert to a simple date, ie, Jan 1, 2022; Feb 1, 2022; Mar 1,2022...
Then the display in the Pivot Table is formatted or grouped appropriately and Date order is supported.
Another thing to do is change the Pivot Tables data retention to None. Pivot Table options, Data tab, Number of items to retain per field... None.

For the desired calculation, you can experiment with the Show Value as... Difference from Previous. In the Grid, you may wish to have to Sum of amounts, but change the Sigma from Columns to Rows.
Beyond that, you would want to explore PowerPivot and DAX.

 

[MSC]

Thank you, I will try all this out tomorrow!