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.

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

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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.