Pivot Table Help or Formula

  • Thread starter Thread starter Lou
  • Start date Start date
L

Lou

Hello, I have a raw data structure setup as follows for my
pivot table:
*Date
*Period
*Fiscal Year
*Revenue
*COS

What I am trying to accomplish is have a rolling 3 month
and 12 month totals for Revenue and COS. MY kicker is that
I want to just enter the new period and let the 2 other
formula cells do the rest. Currently I have to go in and
add the new period to the report from the drop down, them
I have 2 Calculated Items set up that I have to add and
subtract the perids from, 3 month and Rolling 12. Any Idea
on how I can get the 3 month and rolling 12 to auto
calculate in the pivot table.

When I add the new period I need the 3 month to add the
new and drop the last, example.

period 3 3 month includes, P3-P2-P1 in the report.
period 4 would then Add P4, Drop P1 for 3 month avg.

Same holds true with rolling 12..

Any help would be great. I do have a calculated fields for
the 3 month and 12 month and a COS Percentage they feed
off of...

Thank you.
Lou
 
As answered in .newusers --

One solution would be to add a column (RollMonth) to your source table,
and calculate the months difference from the current month. I named a
cell 'MonthStart', and entered the following formula:
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
to calculate the first day of the current month.

Then, in the RollMonth column, where the period date is in column A,
enter the following formula in row 2:

=IF(MonthStart<A2,"--",IF(DATEDIF(A2,MonthStart,"M")>11,"--","M-"&TEXT(DATEDIF(A2,MonthStart,"M"),"00")))
and copy down to the last row of data.

Refresh the pivot table, and add the RollMonth field.
Create a calculated item for the field, e.g.: =('M-00'+'M-01'+'M-02')/3
 
Thank you DD' I think you got me going in the right
direction here. I am working it now, but it makes sense to
me wich is a good thing being my newness to pivot tables.
 
Back
Top