Date Grouping - PowerPivot - Excel 2013

[DrDaeMoN]

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.

 

[Jacob Barnett]

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

 

[leramone]

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 !

 

[scottsen]

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...

 

[leramone]

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!

 

[scottsen]

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.

 

[leramone]

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 !

 

[RoryA]

It always amuses me that I am top 10% in power pivot, but bottom 10% in "normal excel"

If you even know what a pivot table is, you're nowhere near bottom 10% of "normal" Excel!

 

[rodwhiteley]

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.