Columns by Month in Pivot Table

[rjbinney]

Dear Mr. Excel:

If this is answered elsewhere, I can't find it (I keep finding the same answers to different questions...!)

I have a long table of financial transactions:

• Date
• Account
• SubAccount
• Amount

I'd like to build a table - similar to an Income Statement - where the SubAccounts are totalled by month.

I'd like the SubAccounts in Rows and each Column is one month.

So my Pivot Table is:

The layout is great, but no matter what I do, I can't figure out how to consolidate the Date column into months.

(I have confirmed that the underlying data column is formatted correctly (ISNUMBER)).

All my Googling indicates "Group" is the answer - but it seems like I have to go and manually assign each bundle of dates into its appropriate group? That's five years...

I'm sure it's something easy?

 

[rjbinney]

The only "field" list I know is the one that's in my OP (which is also in the far right of the "What It Returns" part of the composite grab above)... What else do I need a pic of?

I just did Insert=>Pivot Table=>From Table/Range

 

[Alex Blakenburg]

If you created it using Table/Range then its a normal Pivot and not a data model pivot.
The field list I am referring to, is the box on the left in the image below. The little table icon indicates it is using the Data Model.

As an experiment using a copy of your workbook and having it open
• then Create a new workbook (ctrl+N)
• Cut your current pivot and paste it into the new workbook
• Try right click on a date and group
• If that works that Cut it from the new workbook and paste it back to where it was.

 

[felixstraube]

When you first drag the field Date to Columns in the field list:

Are Year, Quarter and Month generated?

I tried it adding the data to the model and without add the data, and in both cases the are generated.

 

[Alex Blakenburg]

@felixstraube - that is a setting you can turn on and off in File > Options > Data
Then the last item in the first section > Data options > Disable automatic grouping of Date/Time in PivotTables.
If right clicking on the field and selecting group doesn't work, I would have thought that it won't do anything either but it could be worth trying (you need to untick the box)

 

[felixstraube]

Isn't grouping what he needs?

Another solution I can think of is using the data model.
1. Select the table, in Data/Get & Transform data/From Table/Range. When Powe Query Editor opens, click on arrow down in Close & Load, and select "Close & Load to...". There select "Only Create Connection" and at the bottom make sure "Add this data to Data Model" is checked. Click ok.
2. Go to Data/DataTools/Manage Data Model.
3. Select Data View (if it isnt already selected). And click the first cell in the "Add Column" and write

=FORMAT(Table1[Date], "MMM-YY")

(My table name is Table1, replace with your table name)
And rename that column to Month-Year for example.
Add another column editing the first cell with:

=date(year(Table1[Date]), month(Table1[Date]), 1)

And rename it to SortHelperColumn for example
Then select column Month-Year and go to the menu "Home/Sort and Filter/Sort by column", and select SortHelperColumn in the "By" column.
Data model is now ready.

From the data model manager (aka Power Pivot) got to PivotTable and place your pivot table where you need.
And use Month-Year in your column field.

Let me know if this works for you. Or if you need any help setting this up.

 

[rjbinney]

If you created it using Table/Range then its a normal Pivot and not a data model pivot.
The field list I am referring to, is the box on the left in the image below. The little table icon indicates it is using the Data Model.

View attachment 114906

My screen does not show a "Table1" dropdown like that. The only things are what's in the screenshot. I must not be understanding what you're asking?

When you first drag the field Date to Columns in the field list:

View attachment 114959

Are Year, Quarter and Month generated?

I tried it adding the data to the model and without add the data, and in both cases the are generated.

Nope, none of that is generated. The screen shot I posted is what I get.

Isn't grouping what he needs?

Another solution I can think of is using the data model.
1. Select the table, in Data/Get & Transform data/From Table/Range. When Powe Query Editor opens, click on arrow down in Close & Load, and select "Close & Load to...". There select "Only Create Connection" and at the bottom make sure "Add this data to Data Model" is checked. Click ok.
2. Go to Data/DataTools/Manage Data Model.
3. Select Data View (if it isnt already selected). And click the first cell in the "Add Column" and write

=FORMAT(Table1[Date], "MMM-YY")

(My table name is Table1, replace with your table name)
And rename that column to Month-Year for example.
Add another column editing the first cell with:

=date(year(Table1[Date]), month(Table1[Date]), 1)

And rename it to SortHelperColumn for example
Then select column Month-Year and go to the menu "Home/Sort and Filter/Sort by column", and select SortHelperColumn in the "By" column.
Data model is now ready.

From the data model manager (aka Power Pivot) got to PivotTable and place your pivot table where you need.
And use Month-Year in your column field.

View attachment 114965

Let me know if this works for you. Or if you need any help setting this up.

I got what I needed (and in my "What I Want" screenshot) by futzing with the formulas and creating a column that essentially converted every date to the first of its month. I was hoping to figure out why it didn't give me the options I wanted WITHOUT having to put in a lot of workarounds.

Yes, Grouping is what I would like... It's just not there.

 

[rjbinney]

As an experiment using a copy of your workbook and having it open
• then Create a new workbook (ctrl+N)
• Cut your current pivot and paste it into the new workbook
• Try right click on a date and group
• If that works that Cut it from the new workbook and paste it back to where it was.

Nope; same results.

 

[Alex Blakenburg]

My screen does not show a "Table1" dropdown like that. The only things are what's in the screenshot. I must not be understanding what you're asking?

When you click in a pivot the pane on the right normally has 5 boxes one of which is the field box that shows the available fields/columns you can use in the pivot table. Since you only have 4 you would need to use the cog wheel in the right top corner and pick one of the options that shows the Field list.

Are you able to share your workbook via Google drive, Drop box, One drive or some other sharing platform ? You just need to set it so anyone with the link can access it and post the link here.
You can make every field apart from the date field rubbish but ideally have the date field untouched so that we experience the same issue that you are having.