Pivot Table Slicer Field names (Data model vs Non Data Model)

Hello all!

I have created a very nice large sales dashboard using Pivot tables and charts. At some point to fix an issue I was having I had to switch to using the "Data Model" type tables instead of the normal. This solved one issue but it introduced a new one.
With the normal pivot table non data model, I was able to change the field names in the pivot table itself and it would update slices. SO for instance on my dates field, that was grouped by Month, excel changed them to abbreviations like Jan, Feb, Mar and I was able to manually type in January, February ,March, etc.

Now when I switched to the data model type, when I use groups they are auto named and I cannot change them. Is there any way to do this? The only way I can think of is if in my source data I split out my days by Month, day and year instead of using one field. This is super inconvenient but figured I would ask.

The abbreviations are fine for now - but this is more a question just for learning purposes.

Thanks in advance!

Hi There,

I am quite new to using data model in Excel, but have played around with it exactly as you have. What I have learned to do is the following.

Create a table called calendar, here you make a date column that is the full date, start with the earliest date you will use and drag down to the latest, yes this will have many rows. From here you can then create helper columns, like quarter, month, day what ever you need, using formulas so that is easy to maintain. For this example you would only need one column additional to the date column, which is month, and you want the month number to show.

Then, Create a Month table. Here you can have three columns (for example), an ID column with the month number, a Short name with "Jan/Feb/etc" and a Fullname with "January/February/etc.".

Now, in your data model, create a relation between your date column in your "data" to the date column in the Calendar, Create another relation to the month number in the calendar table to the ID column in the month table.

Now recreate your pivot, using the "Fullname" from the month table.... then recreate your slicer...

Hopefully I understand your problem correctly and that this helps...

Hi,

Here is a small and digestible example for you. Take a look and try to use this in your file.

Also look at this: https://sharepointmike.wordpress.com...ny-other-name/

In my example, you could then experiment with adding Weeknumber in the Calendar table. Just
1. Add a column called weeknumber
2. Add a formula for the weeknumber

Refresh data so that it appears in the pivot field list. Then add it to the pivot table.

Good luck!