Wrong dateformat and month sorting in pivottable

Question

I have an issue with dateformat and sorting months in a pivottable.

I have created a sample file with two pivot tables in it (sorry its in Dutch).

• one WITHOUT adding to the data model => shows the months in the correct format (JAN FEB etc) and correct sequence
• one WITH addition to the data model => does not show the months as a three-letter abbreviation, but as a date format with the first of the month as the day (1-2-2017, 1-3-2017, etc.)

The data table is filled with data that directly from an ERP system.

What I can not figure out is how to get the date format right when I add the pivot table to the data model.

I need that because there are multiple pivot tables in the original spreadsheet based on multiple data tables. These are all linked (relationship) to organizational unit.

With that, I can adjust all the graphs at once to the selected team with one slicer.

I'm curious if anyone has an answer to this.

I work with Office 2013, without power BI and without power query.

I have created a model but can not add it here.

I just add two screen prints to show the diffference.

One when I add the pivot to de the datamodel and one without adding the pivot tot the datamodel.

The one I want to see is the formatting  als in the model without adding the pivot tot the datamodel.

What do I have tot do to get the pivot added tot the datamodel and showing the celformatting of the months as a three letter abbriviation?

Pivot WITH adding to datamodel.

Pivot WITHOUT adding to datamodel.

Answer 1

Hi,

Check below how did i solve this issue.

1. Ungroup your pivot table
2. Group again according to your preference, then
3. Click the filter of your pivot table in row label.
4. Click more sort options
5. Click Manual, then ok

Let me know if that helps.

Kind Regards,

Engr. Jamshed Muhammad

Answer 2

Your screenshots didn't come through. Can you try to repost them?