Date Grouping - PowerPivot - Excel 2013

DrDaeMoN

New Member
Joined
Jun 17, 2012
Messages
8
Hello all. I'm using Excel 2013 to create a new dashboard. Currently I can't group date in powerpivot tables the option is disabled while I can do this for sure with the regular pivot tables for the same data set. In another file I created earlier (using Excel 2010 and PowerPivot 2, later upgraded to Excel 2013 format) I can group date in powerpivot tables. There are no blank cells in the date field and of course some repeated dates are available in the 2 files (the working and the new one). Your help would be deeply appreciated.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
DrDaeMoN,

I know that you can't group on a 2010 PowerPivot pivot table and as I'm still easing myself into 2013 I'm not totally sure but I suspect its the same. In truth its a feature that although convenient in standard pivots is rendered obsolete by following accepted best practice in building PowerPivot models by setting up a separate date table.

This table should have a column which consists of unique, contiguous dates for the relevant period. You will then relate this table by the date to your fact (data) tables. You should also use the 'Set as Date' functionality in the design tab.

Reasons to do this include:


  • The date table can include much more than just a column for dates! You can have columns for years, months, weeks, quarters etc. Not only does this solve your inital issue in a far more powerful way but means that you can build your company's fiscal calendar into the table.
  • Such a table is necessary to use many of the inbuilt time intelligence functions in DAX.
  • One of the key benefits of PowerPivot is the ability to combine data sets - only by relating each of those data tables to a single calendar by the date can they be truly integrated.

Rob's post on this issue from way back when is pretty informative: The Ultimate Date Table « PowerPivotPro
 
Upvote 0
So, unless I use the 'best practices' of using powerpivot tables (which will imply in design and link a date table) is really NOT possible to group by dates?


My best regards !
 
Upvote 0
While having a date table is certainly a good idea, it shouldn't be "required". Well, except I don't actually know what is meant by "group by". You just mean dragging the date field to "rows" of your pivot table!? cuz that should certainly work...
 
Upvote 0
Hello Scottsen o/

Thanks for your reply!


The 'group by' is about grouping a date field (already dragged to the 'row field' of the pivot table (or column too)) by its days, quarters, months, etc.

The link about the 'group by' is here: Grouping Dates in Pivot Tables - Show Pivot Reports by Month, Quarter, Week or Hour of Day | Chandoo.org - Learn Microsoft Excel Online


and it's seems that one does not simply is possible to group by months (or days, or quarters) anymore. I'm using Excel 2010 with the last version of PowerPivot


See ya!
 
Last edited:
Upvote 0
Hey, that is pretty cool! :) It always amuses me that I am top 10% in power pivot, but bottom 10% in "normal excel" :)

The normal way to handle this in power pivot is by custom calculated columns (typically on your separate calendar lookup table).

So, if you had a date column... you could derive a Year-Quarter (2011-Q1, etc) column, and group by that.
 
Upvote 0
LOL


Although it's a kick in the butt for me (yes, I have several databases to 'create that calculated column' ¬¬ ) By the way, I'm trying, as aforementioned, link all these table (through primary keys) with a standard 'Date Table' (with all those date dimensions)!

=D

Futhermore, I'm looking for a Qlikview solution for what I'm supposed to do.


My best regards !
 
Upvote 0
At present it's not possible to use the "Group By" Pivot Table functions in PowerPivot. The workaround involves making a custom date table (spanning the entire range of interest) and then a few steps more.
Mike Girvin (excelisfun) shows it well here, and also clearly shows when you should simply stick to Pivot tables instead of PowerPivot.
https://www.youtube.com/watch?v=LkDbHRCZ4mY
Not impossible, just clunky.
 
Upvote 0

Forum statistics

Threads
1,222,313
Messages
6,165,293
Members
451,950
Latest member
WH2000

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