Organizing Pivot Table by Year/Month - Rolling 12 Months

**method90** · 03-04-2020, 11:18 AM

Hi,

I'm trying to create a rolling 12 months pivot table/chart but it's always grouping together JAN/FEB 2019/2020 for example. How can I only show JAN/FEB 2020 and start 2019 from March? (12 months)

I've tried coupling the Year/Month values but they always come out mixed and I'd have to reorganize them in the table.

rolling12.jpg

Thanks in advance.

**MarvinP** · 03-04-2020, 11:27 AM

Hi method90,

I think you simply need to use the Row Labels dropdown and uncheck what you don't want. See the attached. Or am I missing something in the question?

Average Lag PT Group Row Filtered.xlsx

**method90** · 03-04-2020, 11:31 AM

But when I uncheck JAN it'll uncheck JAN on 2019 and 2020.

**JeteMc** · 03-06-2020, 10:38 PM

Building on Marvin's suggestion here are two options:
1. Use columns A:B from the source data and set date filter to After 3/1/2019 (Pivot 1)
2. Add a helper column (C) to the source data, populated using: =A2>=EDATE(TODAY(),-12) and then use the helper column as the filter(Pivot 2)
Note that both options will need to be Refreshed, but option 2 will not require adjusting the filter/date filter.
Let us know if you have any questions.