Rolling average in pivot tables

[Gojia_matt]

Hi all,

After years of stalking this forum for solutions, I've finally come across a problem that I couldn't find the answer to. Thank you very much in advance for your help.

I am trying to add a rolling average to my pivot table. In the example below, 'chronic load' is a rolling four week average of the previous four weeks' values in the 'sum of load' column. I did these calculations outside of the pivot table (i.e., the 'chronic load' column is on the side of the pivot table). However, I would like to be able to do the calculations within the pivot table - presumably by using the 'calculated field' function. My issue is that I do not know the formula for using values from other rows in my formula. Please let me know if you need any more information.

Any help very much appreciated.

[TABLE="width: 282"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Values
[/TD]
[TD]Chronic load[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Sum of Load
[/TD]
[/TR]
[TR]
[TD]20-03-17 - 26-03-17[/TD]
[TD="align: right"]840[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27-03-17 - 02-04-17
[/TD]
[TD="align: right"]1330[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03-04-17 - 09-04-17
[/TD]
[TD="align: right"]1159[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10-04-17 - 16-04-17
[/TD]
[TD="align: right"]729[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17-04-17 - 23-04-17[/TD]
[TD="align: right"]713[/TD]
[TD="align: right"]1014.50[/TD]
[/TR]
[TR]
[TD]24-04-17 - 30-04-17[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]982.75[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]695.25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]540.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]446.50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]180.00
[/TD]
[/TR]
</tbody>[/TABLE]