Ordinary Pivot Table vs Power Pivot table group by dates

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
439
Office Version
  1. 365
Platform
  1. Windows
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)
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.
 
Upvote 0
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:

1619424533308.png




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.


1619424707931.png


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.

1619425179311.png
 
Upvote 0
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.

1619427763197.png
 
Upvote 0
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?
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top