Sort dates in Pivot Table

Question

I need to sort the dates from latest to the last.

How to do that in Pivot table 

Answer 1

Your dates appear to have been formatted as Text.

I’m assuming that you don’t have a large amount of data.

If I format the dates that you mention as:-

ddd mmm d/yy

 - in my underlying data and then set up a Pivot Table based on that it automatically sorts the data in ascending order of dates (from oldest to newest).

If I then want to re-sort the data so that the dates are in descending order (from newest to oldest) I click on the drop down arrow to the right of:-

Row Labels

 - and then click on:-

Sort Newest to Oldest

 - to show (see screen shot below).

 

Answer 2

I changed it to Date from Text. But Pivot drop down saves the number as 1/10/2021. I am able to format it to 10-Jan-2021 but the same won't reflect in the new slicer. Date format needs to be same in the Slicer to avoid confusion.

Answer 3

SLICER FORMATTING

• I can't reproduce that:-

Answer 4

You've identified a couple of issues.

.

For PivotTables to use dates as dates, it works best if provide a continuous series of dates, aka a "Date Table".

.

Excel, including PivotTables handles dates in 2 ways. First, for dates to be handled as dates, they have to be defined as a date data type. The root of the Date data type is a serial number starting from Jan 1 1900. Today's date (Mar 2, 2021) serial number is 44257.

.

In Excel, not just PivotTables, the underlying Date data type is modified by custom display formatting.

.

@ Deal with Dates in a Pivot Table     
https://www.got-it.ai/solutions/excel-chat/excel-tutorial/pivot-table/learn-how-to-deal-with-dates-in-a-pivot-table
We can customize our pivot table dates to be displayed in different ways. In simple steps, we will explore how to sort, filter, group and format our pivot table dates easily.
.  *  Setting up the Data
.  *  Group Pivot Table Dates
.  *  Sort Pivot Table Dates
.  *  Insert a Timeline
.  *  Change Date Formatting In Pivot Table
.  *  Filter For a Specific Date Range
.

Note: there even are a couple of built in "M" language functions to generate Calendar Tables, but I think this next tip is much better, once you wrap your mind around the concepts.

.

4 Ways to Fix Date Errors in Power Query + Locale & Regional Settings 2020 04 29              Jon Acampora
https://www.excelcampus.com/powerquery/power-query-date-errors-settings/
Learn 4 different ways to fix date data type errors in Power Query, including with locale, regional settings, and custom formulas with Column From Examples. Sometimes in Power Query, when you attempt to format data as a date, you will receive error messages. This is because Power Query is unable to recognize the data. The most common occurrence for this is when the original format of the date is from a different region.
.  1. Locale in Data Type Menu
.  2. Locale in Regional Settings
.  3. Operating System Regional Settings
.  4. Custom Formula with Column From Examples
.

.

If you want to learn a couple of neat tricks, take a look at the free webinar I mention here:

https://answers.microsoft.com/en-us/msoffice/forum/all/powerquery-radical-new-concept-you-can-use-queries/62d98e76-ba7e-458b-8a5e-9b39cdfd968a

There is a segment about using PowerQuery to DYNAMICALLY generate the required date table, BASED ON your input data(!). The technique is really "nifty", and so is the underlying concept described in the video.

.

Here are a couple more articles describing creating date tables

Calendar Date Table        2012 05 17
https://powerpivotpro.com/2012/05/excel-5-calendar-date-table/
If you are working with data derived from a data warehouse, a date table is perhaps the most common dimension table that exists in the warehouse. However, not every scenario involves working with a data warehouse directly, and I simply wanted a “portable” date table. I found very little online, the best perhaps being **this Excel table offered by the Kimball group** (the table has been expanded since I originally downloaded it). I could have modified the Kimball table for my particular needs, but I decided to create one from scratch.  Late last year, Rob posted an article titled the Ultimate Date Table, which is available from the Azure Marketplace. I considered using this table instead of the one I was building in Excel, but the “Ultimate Table” lacks fiscal periods. Much of the analysis work I do includes fiscal periods.
.

Date Table- Creating a simpler and chart-friendly Date table in Power BI PowerQuery DAX
https://www.sqlbi.com/articles/creating-a-simpler-and-chart-friendly-date-table-in-power-bi/?nu=66531
A Date table in Power BI can have a smaller number of columns by leveraging custom format strings to adequately control the chart visualization and the sort order.
.  *  Implementing a classic Date table
.  *  Implementing a simpler Date table
.  *  Limitations and additional insights
.  *  one warning related to the differences between the FORMAT syntax used by Power BI and in DAX.
.

.

!   Custom Date / Time format codesEvery “Date” has implicit time attached, and every “Time” has implicit date attached.
Date CodesThe letters d, m, and y are used to represent days, months, and years in Excel date formats.
Date Mask    Displays.  d                days (1-31) without a leading zero
.  dd              days (01-31) with a leading zero
.  ddd            days (Sun-Sat) using 3-letter abbreviations
.  dddd          days (Sunday-Saturday) full day spelled out
.  m               months (1-12) without a leading zero
.  mm            months (01-12) with a leading zero
.  mmm         months (Jan-Dec) using 3-letter abbreviations
.  mmmm      months (January-December) full month spelled out
.  mmmmm   months (J-D) the first letter of the month
.  yy               years (00-99) last two-digits of year
.  yyyy            years (1900-9999) all four-digits of the year
.
Personally, I'd avoid the mmmmm format. How can you (or anyone else reading your worksheet) distinguish between January, June, and July, or between March and May, or April and August when looking at dates individually?
.
!   Format a date the way you want
https://support.microsoft.com/en-us/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e
When you enter some text into a cell such as "2/2", Excel assumes that this is a date and formats it according to the default date setting in Control Panel. Excel might format it as "2-Feb". If you change your date setting in Control Panel, the default date format in Excel will change accordingly. If you don’t like the default date format, you can choose another date format in Excel, such as "February 2, 2012" or "2/2/12". You can also create your own custom format in Excel desktop.
.  *  Choose from a list of date formats
.  *  Create a custom date format
.  *  Tips for displaying dates
.
Time CodesThe letters h, m, and s are used to represent hours, minutes, and seconds in Excel time formats. Additionally, you can specify elapsed time and AM or PM in a custom format -
Time Mask         Displays.  h                        hours (0-23)   without a leading zero
.  hh                      hours (00-23) with a leading zero
.  m                       minutes (0-59)   without a leading zero
.  mm                    minutes (00-59) with a leading zero
.  s                        seconds (0-59)  without a leading zero
.  ss                      seconds (00-59) with a leading zero
.  [h]:mm              elapsed time in hours      (such as 28:30), Optional hours
.  [mm]:ss             elapsed time in seconds   (such as 65:20), optional Minutes
.  [ss]                    elapsed time in seconds (such as 90)
.  ss.000                Decimal seconds to thousands
.  [h]:mm:ss.000    Decimal Seconds to thousands, optional hours
.  h:mm AM/PM     hours (such as 6:00 AM or 6:45 PM)
.  h:mm A/P           hours (such as 6:00 A    or 6:45 P)
.
NOTE:
.   When a time format does not contain AM/PM or A/P the display is on the military 24hr clock.
.   If the format contains AM or PM, the hour is based on the 12-hour clock.
.      "AM" or "A" indicates times from midnight until noon; 12 AM is Midnight.
.      "PM" or "P" indicates times from noon until midnight; 12 PM is Noon.
.
Sample Custom Date And Time CodesThese are just a few examples to give you some ideas of what is possible with custom date and time formats. To display text next to a format code, enclose the text in quotes.

CODE                                  DISPLAYS.  d mmm yyyy                     3 Apr 2000
.  mmm/dd/yyyy                   Apr/03/2000
.  mmmm dd, yyyy (ddd)      April 3, 2000 (Mon)
.  hh:mm "GMT"                  14:15 GMT
.  [h]:mm "elapsed hrs"        28:30 elapsed hrs
.  h A/P                                8 A
.  [blue]dd-mm-yyyy             03-04-2000  (displayed in blue text)
.

!   Date and Time Intro         2020 10 17    Mynda Treacy
https://www.myonlinetraininghub.com/excel-date-and-time
The objective of this post is to teach you how Excel handles date and time and provide you with all the tools you will need.
It’s designed to be read in conjunction with the accompanying Excel file, which you can download.
.  *  Windows Regional Settings
.  *  Excel Date and Time 101
.  *  Dates
.  *  Time
.  *  Date & Time Together
.  *  Entering Dates
.  *  Entering Times
.  *  Entering Dates & Time together
.  *  Adding/Subtracting Days from Dates
.  *  Subtracting Dates from one another
.  *  Adding Times to one another
.  *  Subtracting Time from Times
.  *  Subtracting Times from one another
.  *  Date and Time Shortcuts keys
.  *  'Good to Know' Stuff about Excel Date and Time
.  *  Date Modes: 1904 vs 1900
More links:
.  *  Every Excel Date and Time Function explained
.  *  Formatting Date and Time in Excel
.  *  Common Date and Time Calculations
.

Format numbers as dates or timeshttps://support.microsoft.com/en-us/office/format-numbers-as-dates-or-times-418bd3fe-0577-47c8-8caa-b4d30c528309
When you type a date or time in a cell, it appears in a default date and time format. This default format is based on the regional date and time settings that are specified in Control Panel, and changes when you adjust those settings in Control Panel. You can display numbers in several other date and time formats, most of which are not affected by Control Panel settings.
.  *  Display numbers as dates or times
.  *  Create a custom date or time format
.  *  Tips for displaying dates or times
.

Answer 5

I changed it to Date from Text.

Did you change your underlying data's format? If I do that it correctly filters through to both Pivot Table and Slicer.