Calculating Increasing Values by Date in a Pivot Table

[puscypher]

Brand new poster here - long time user of MrExcel.com. I've always been able to get my questions answered here, but this is the first time I've not been able to find a solution with a quick search. Any help is more than appreciated!

I have multiple pivot tables that are reporting to individual tables based on calculations.

Currently, I have an ever-growing pivot table of dates reporting values.

I have multiple formulas that I am using which are coincidental with the dates as they move.

I am wondering if there is a good formula that will always keep track of the day as it is added and report on its corresponding value.

For instance, these values are being recorded by a running total, a running average, and a 30 day rolling average.

[TABLE="width: 452"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]29-Jul[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]388[/TD]
[TD="align: right"]488[/TD]
[/TR]
[TR]
[TD]30-Jul[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]343[/TD]
[/TR]
[TR]
[TD]31-Jul[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]348[/TD]
[/TR]
[TR]
[TD]1-Aug[/TD]
[TD="align: right"]172[/TD]
[TD="align: right"]433[/TD]
[TD="align: right"]605[/TD]
[/TR]
[TR]
[TD]2-Aug[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]237[/TD]
[TD="align: right"]242[/TD]
[/TR]
[TR]
[TD]3-Aug[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]218[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]4-Aug[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]252[/TD]
[TD="align: right"]320[/TD]
[/TR]
[TR]
[TD]5-Aug[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]217[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]6-Aug[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]154[/TD]
[TD="align: right"]199[/TD]
[/TR]
[TR]
[TD]7-Aug[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]158[/TD]
[TD="align: right"]209[/TD]
[/TR]
[TR]
[TD]8-Aug[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]299[/TD]
[/TR]
[TR]
[TD]9-Aug[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]276[/TD]
[TD="align: right"]357[/TD]
[/TR]
[TR]
[TD]10-Aug[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]217[/TD]
[TD="align: right"]268[/TD]
[/TR]
[TR]
[TD]11-Aug[/TD]
[TD="align: right"]147[/TD]
[TD="align: right"]308[/TD]
[TD="align: right"]455[/TD]
[/TR]
[TR]
[TD]12-Aug[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]288[/TD]
[/TR]
[TR]
[TD]13-Aug[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]156[/TD]
[/TR]
[TR]
[TD]14-Aug[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]134[/TD]
[TD="align: right"]163[/TD]
[/TR]
[TR]
[TD]15-Aug[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]209[/TD]
[TD="align: right"]256[/TD]
[/TR]
[TR]
[TD]16-Aug[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]85[/TD]
[/TR]
</tbody>[/TABLE]

There is a grand total field at the bottom, because it's a pivot table. I'm wondering how I can ensure that my formula is set to always accommodate the new line item for my over-time calculation, and also for the rolling 30 day (meaning that I am only calculating the most recent 30 values).

 

[puscypher]

I figured it out... for those of you who may encounter something similar, you can enter the following to calculate for each:

=SUMIF(A:A,">"&TODAY()-30,B:B) - that will give a rolling 30 day as dates increase.
Also, replace SUMIF with AVERAGEIF to get a rolling 30 day average.

I hope this helps anyone else.

 

[puscypher]

Oh - and you'll want to remove the Grand Total from the Pivot Table to ensure that it is not including it in your calculation.