Ordinary Pivot Table vs Power Pivot table group by dates

[kcmuppet]

Hi all

A pivot table using power pivot isn't letting me group by dates properly. (If I create the pivot table from the same data using the ordinary pivot table, everything works properly, but If I create it using power pivot the dates are not handled properly.)

Creating the pivot table from power pivot, If I drag the date field to the field list, Excel knows its a date because it automatically adds grouping by Year, Qtr, Month and 'Date', but..

• It does sort properly (If I group by month, and then sort ascending it puts January first, but ignores the year, so when I have data say from Oct 2020 to Feb 2021 and sort as ascending by date, the column sequence is: Jan, Feb, Oct, Nov, Dec, instead of as it should be: Oct, Nov, Dec, Jan, Feb
  
  
• It doesn't let me change the number of days if I group by day

The source of both the ordinary pivot table and the power pivot data is an excel table in which the date field is formatted as a date in a excel table a 'Date', Type *14 02 12

In the power pivot data view the Data Type is 'Date' and the Format is *14 Mar 01

In the query upon which the power pivot data is generated, the date is formatted as Data Type: 'Date'

What could be causing power pivot tables not to work properly in this respect?

(I'm using Excel 365)

 

[Alex Blakenburg]

Creating the pivot table from power pivot, If I drag the date field to the field list, Excel knows its a date because it automatically adds grouping by Year, Qtr, Month and 'Date', but..

• It does sort properly (If I group by month, and then sort ascending it puts January first, but ignores the year, so when I have data say from Oct 2020 to Feb 2021 and sort as ascending by date, the column sequence is: Jan, Feb, Oct, Nov, Dec, instead of as it should be: Oct, Nov, Dec, Jan, Feb
  
  
• It doesn't let me change the number of days if I group by day

1. If you have grouped by Year and Month and you only include Month it in the Pivot Table it will consolidate the month's across the years, it will also only use month in the sort.
   This is obviously not what you are wanting to you need to include the Year in the output of your Pivot Table.
   
   
2. If you group by multiple levels eg Days AND Month, then the higher level groups eg Month overrides any Days grouping so it doesn't let you select the number of days to group by.
   To group by days you need to have that as your only grouping selection.

 

[kcmuppet]

Alex, Thanks for your reply.

When the pivot table or chart is built directly from the source table, as soon as I drag the date to the axis categories, a 'Years' field is created which when expanded in the table expands to months and the chart shows the dates in the ascending order:

Whereas, if I build it from the Power Pivot, 3 additional fields are created '(Year)', '(Quarter)' and (Month)', but expanding the year field in the table, expands all dates, not by month. Removing '(Quarter)' from the axis categories still showed all dates. To create a chart with months in the right sequence, I had to drag not only quarters and Event date away, but also years and then I had to drag it back, which produced nearly the chart that I wanted.

However, I'm still not able to change the number of days - even when that is the only thing selected for grouping.

I also found that when building the chart from the power pivot I'm not able to change the legend (series) sequence.

 

[Alex Blakenburg]

However, I'm still not able to change the number of days - even when that is the only thing selected for grouping.

It doesn't look like you can group on days in Power Pivot per the Mynda Treacy on link below.

https://www.myonlinetraininghub.com/excel-forum/power-pivot/dates-not-grouping

 

[kcmuppet]

It doesn't look like you can group on days in Power Pivot per the Mynda Treacy on link below.

https://www.myonlinetraininghub.com/excel-forum/power-pivot/dates-not-grouping

View attachment 37515

Thanks for confirming. It also looks like you can't changes series order either.

 

[Alex Blakenburg]

Whereas, if I build it from the Power Pivot, 3 additional fields are created '(Year)', '(Quarter)' and (Month)', but expanding the year field in the table, expands all dates, not by month. Removing '(Quarter)' from the axis categories still showed all dates. To create a chart with months in the right sequence, I had to drag not only quarters and Event date away, but also years and then I had to drag it back, which is what I wanted.

I didn't seem to have your issue with the expanding of the dates.
(The bottom right is the data model view)
I seem to be able to replicate what you get if I also drag the actual date field into the pivot table.
If I only include year and month its fine.

 

[kcmuppet]

I didn't seem to have your issue with the expanding of the dates.
(The bottom right is the data model view)
I seem to be able to replicate what you get if I also drag the actual date field into the pivot table.
If I only include year and month its fine.

View attachment 37519

Thanks for trying it out. Are you able to change the series order?

 

[Alex Blakenburg]

Thanks for trying it out. Are you able to change the series order?

My sample data had products as columns in the Pivot. Reversing the sort order of the products flowed through to the chart.
I haven't tried it where the columns are different fields and changing the order of the columns around on them.

Do you want to see if changing the column order does what you need ?

 

[kcmuppet]

Thanks for the suggestion. I found that if I created a pivot table first, and then a pivot chart from it, I could then manually drag the series categories in the table, which then changed the order in the chart. It's a shame it just doesn't work / the move up & down buttons are greyed out in the pivot chart created directly from power pivot

 

[Alex Blakenburg]

Thanks for letting me know. Glad you found a way to get the desired output.