Slicers - Proper Month & Day Order

Hello,

I've added 3 slicers "Year" "Month" "Day" to my pivot table, how do I get the months to display in order of Jan, Feb, Mar etc and the days to display 1,2,3,4,5 etc. It appears the slicer settings ascending and descending sort is sorting the months and days by alphabetical order based on the name of the month and day.

Thanks
Daren

Hi darenferg,

I think it may has to to with the formula you are using in your Table in columns Z & AA. You basically formatting the value as text.

Try using : =MONTH([@[CLOSE-DATE]]) & =DAY([@[CLOSE-DATE]])

This will give you numbers 1-12 for the months and 1-31 for the days. Refresh the Slicers and you should get the correct sorting from 1 upwards.
Drawback is that your month will be a number (1-12) instead of the 3 letter short form (Jan, Feb, Mar.... Dec)

Hi again,

For your month; column Z, Try: =TEXT(MONTH([@[CLOSE-DATE]]),"00")&" "&CHOOSE(MONTH([@[CLOSE-DATE]]),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Regards,

Hi Daren,

The formula should return the month number in 2-digit, hence for example 01 Jan, 02 Feb....
Yes it is text, but with the number at the front should still sort correctly.
See attached file.

1. Ribbon Power Pivot > manage data * enable Power Pivot ribbon at Options > Add-ins> Com Add-ins *

2. Add column Month no =month(Table1[CLOSE-DATE])

3. Select column Month, Ribbon Home > sort by column > Sort Month by Month No

4. Right-click at Month slicer > sort Data Source Order

Power Pivot.png