Pivot Table Sorting Issue - Power Query

Question

Hello,

I have a query in Excel that generates a table. In this table, I’ve created a computed column called “Date Category” to classify months based on today’s date.

For example, if today’s date is 10th November 2025:

All dates before 1st November 2025 are ignored.

Dates from 1st–30th November are classified as November.

Dates from 1st–31st December are classified as December.

Dates from 1st–31st January are classified as January.

Any dates beyond that fall under the category “Beyond 3 Months.”

I then use this data in a Pivot Table, where Country is in the Rows and Date Category is in the Columns.

The issue is that the Date Category columns do not stay in the desired order after each refresh. The order changes randomly—for example, it might display as January, Beyond 3 Months, November, December instead of the correct sequence.

The desired order should always be: November → December → January → Beyond 3 Months

Or If the todays date is 25th March 2026. March - April - May - Beyond 3 Months

Could you please advise how to fix or enforce this custom sort order in the Pivot Table so it remains consistent after every refresh?

Thank you,

Answer 1

To enforce a custom sort order in your Pivot Table for the Date Category column, you can follow these steps:

1. Create a Custom Sort Order: You need to create a helper column in your original data that defines the sort order for the Date Category. For example, you can assign numerical values to each category:
   • November = 1
   • December = 2
   • January = 3
   • Beyond 3 Months = 4
   This can be done using a formula in Excel that checks the Date Category and assigns the corresponding number.
2. Add the Helper Column to the Pivot Table: Once you have this helper column, refresh your Pivot Table and add this column to the Rows area of your Pivot Table.
3. Sort by the Helper Column: After adding the helper column, you can sort the Pivot Table based on this new column. To do this, right-click on the Date Category in the Pivot Table, select Sort, and then choose Sort by your helper column.
4. Hide the Helper Column: If you do not want the helper column to be visible in the Pivot Table, you can hide it or remove it from the display after sorting.

By following these steps, your Date Category should maintain the desired order of November → December → January → Beyond 3 Months consistently after each refresh.

---

References:

• Sort columns
• Best practices when working with Power Query