I was messing around with the example here on Power Pivot and saw where Excel 2016 automatically does groupings on dates so you don't have to create MONTH or YEAR columns, shown more clearly in the MS announcement last year.
However, this seems to be automatic, so what happens if it doesn't work? In the example file for this exercise, I deleted the DATA table from the data model, then re-added it. When I created a pivot based on it, there was just DATE. There were no other DATE (MONTH) or DATE (YEAR) fields to select.
Is there a way to force it, or is it that if any 2013 file didn't have it, 2016 won't recognize the dates when opened there?
Hi Ehans,
If you open my original file again and look at the Data table in Power Pivot you'll see I had added some calculated columns for the Month and MonthNumber. When you deleted the Data table and added it back again, did you also add these calculated columns back in?
The only way to force Excel 2016 to use your own Date groupings, rather than their automatic groupings, is to put these columns/fields in your data tables, either using calculated columns like I did, or with a Date Table as described in session 3.09 of my Power Pivot course which you are a member of.
Please let me know if you have any questions.
Mynda
Hi Mynda,
I couldn't get the revised 2013 file to dowload - said file didn't exist.
However... I had the original and was playing with it. You can force the new Excel 2016 date grouping. Here is what I discovered:
- I deleted tables from within the PowerPivot Manage Data window to start clean. I could have just deleted your added month, monthnum columns, but enjoy practicing.
- Added both tables to the Data Model in the PPVT tab
- Created the relationships with the Product field
- Inserted the PivotTable into a worksheet tab from the data model. Excel 2016 lets you do this from the Insert|PivotTable button just like other pivot tables. Just check the "use data model" box.
- Added Dates to the Columns, then Category to the Rows.
- Right-clicked on Dates and selected Group. It thought for a minute and decided possible options were seconds, minutes, hours, days, months, quarters, years. I selected Months, Quarters, Years and hit ok.
- Now in my data field selection area in the Pivot Table fields there are:
- Date
- Date (Month)
- Date (Quarter)
- Date (Year)
So much easier than adding custom fields, but I suspect this would blow up if I saved it then tried to open in Excel 2013 that didn't understand these groupings.
It is a little counter-intuitive for the groupings to be in the Pivot Table creation area rather than in the Power Pivot Manage Data Model area, which is why it took me a bit of time to figure it out.
Hi Ehans,
Sorry, I misunderstood your question. I thought you didn't want to use the automatic groupings available in Excel 2016. Yes, they are much easier but they are also limited, especially if you want to insert a Slicer for say, Month-Year...you can't. For this you need a manual month-year column, or you have to put up with a separate Slicer for the Year and another for the Month, which is annoying at best.
And yes, if you try to use the Excel 2016 grouped dates in Excel 2013 it won't recognise them. Otherwise they're handy.
Mynda
Thank you. You just saved me some potential headache with the slicer issue. I'll use the custom columns.
