There must be a more efficient way to do this

[cqcqcq]

Hi,

Each month I am running a basic year over year product category performance report using pivot tables. The data for the current report and pivots are coming from a table that has this year and last year and each month as columns.

Each month, I'm going in and selecting the current month as a new value, and last years coinciding month. Also, along with that I have a calculated field that needs to be changed every month too.

I feel there must be a much easier or better way to do this. Each month I am essentially opening about ten pivot tables, removing the month value, re-adding the new ones, and fixing my calculated fields. It's a tedious task
the attached image may make it a little easier to understand. The product cats have been blurred. This one is fixed for my July report, but before it was fixed it was all set to Jun-19, Jun-20 etc..etc..

Thanks for any help!

EDIT:
Image removed at request of OP

 

[MUKESHY12390]

there two way... if you don't want to touch pivotable you need to add those formulated columns in source file through using formula.

or creating macro would be 2nd option.

 

[cqcqcq]

there two way... if you don't want to touch pivotable you need to add those formulated columns in source file through using formula.

or creating macro would be 2nd option.

If I did the first approach, formulate in the table, are you suggesting to then just make something that has a vlookup for the current month and goes and grabs only the data needed? Maybe that's where I'm stuck, I can make that, but just need the easiest way to swap from month to month

 

[MUKESHY12390]

okay .. tell me criteria for those column, how you decide which month sould be part of pivotable.