Rolling 12 month pivot

[heytherejem]

Hi all,

I have a pivot table which shows totals for four locations, with month and year as a the column to display the last 12 months.

I now have July 2017 data so I want to kick out July 2016. However I can't just de-select July from the column labels drop-down as it also removes July 2017.

I've added a sample of data, is there any way around this?

Thanks,
Jemma

[MarvinP]

Hi Jemma,

Instead of using the Year and Month as your column filters, use the "Date Audited" as the column filter. Then you can filter on the columns and it will allow you to do a Date greater than... See the attached where I've done this for you. I think this will solve your problem.

PT use DateAudit for Columns.xlsx

[heytherejem]

Hi Marvin,

That looks great! How exactly did you do this though, because I need to replicate it and I can't see how you've done it.
When I change the column to Date Audited it's placing each date in a separate column, not grouping by month. And you've also magically got an additional 'Years' option on the Pivot table fields, and options for less than and more than on the pivot table!

What you've done looks good though.

[MarvinP]

Right click on one of the values and then click on group. Unselected quarter.