Pivot Table: Totals for "Last 12 months" and "Preceding 12 months"

[kansaskannan]

I have a Pivot Table which shows revenues for each month in a rolling
24-month period. (That is, the 24 months are not two calendar years,
but start any month and end 24 months later). I need to add:
subtotals for the last 12 months, and the 12-months prior to the last
12 months.

Apr -2001 $2,471,802
May-2001 $2,637,046
...
...
...
Feb-2002 $2,323,360
Mar-2002 $1,328,335

Last 12Month ????????
Prec 12Month ????????

How do I do this with VBA in the Pivot Table itself? Or will I have to
do this in Excel, after the Pivot has been calculated?

Thank you!

 

[kansaskannan]

Thank you, Jim, for your response and advice. Do I understand you
correctly to mean:

Step 1. In Source Data create two columns for Current 12 month and
Prior 12 month totals.
Step 2. Then any month (=row) that I pick will have a corresponding 12
month and 24 month total.

??

 

[Guest]

Not really... Just add a single extra column. Each cell will contain either
the words "Current" or "Prior" based on the date in that row. So it will look
something like this...

Date Amount flag
1-Nov-05 100 Prior
1-Dec-05 200 Prior
1-Jan-06 300 Prior
1-Feb-06 400 Current
1-Mar-06 500 Current

Now you have a field that you can add to your pivot and aggregate by...

flag Date Total
Current 1-Feb-06 400
1-Mar-06 500
Current Total 900
Prior 1-Nov-05 100
1-Dec-05 200
1-Jan-06 300
Prior Total 600
Grand Total 1500

 

[kansaskannan]

Jim,
Appreciate your follow-up, and your suggestion turned out to be just
what was needed.
A big 'thank you'.
kannan