Ordinary Pivot Table vs Power Pivot table group by dates

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
315
Office Version
  1. 365
  2. 2010
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
808
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 2, 2005
Messages
315
Office Version
  1. 365
  2. 2010
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
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
808
Office Version
  1. 365
Platform
  1. Windows
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
 

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
315
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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

Well-known Member
Joined
Feb 23, 2021
Messages
808
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 2, 2005
Messages
315
Office Version
  1. 365
  2. 2010
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

Well-known Member
Joined
Feb 23, 2021
Messages
808
Office Version
  1. 365
Platform
  1. Windows
Thanks for letting me know. Glad you found a way to get the desired output.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,899
Messages
5,655,861
Members
418,248
Latest member
JinxedCaspa

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
Top