Calculating the average of a balance sheet item for quarterly / annual totals
I have a number of balance sheet accounts (e.g. Closing Receivables*) for which I need to calculate the average value.
This should be calculated as follows:
 Monthly: average between the current month and prior month value
 Quarterly Total: average of the last 4 data points
 Annual Total: average of the last 13 data points
*note that Closing Receivables will have 'Sum, Time: Closing Balance' as its aggregation setting
Below is a worked example:
Currently my method to do this seems complex:
 Create an 'Opening Receivables' line item with calculation 'previous(Closing Receivables)' and aggregation 'Sum, Time: Opening Balance'
 Create a 'Closing Receivables (time sum)' line item with calculation 'Closing Receivables' and aggregation 'Sum, Time: Sum'
 Create a 'Time Aggregation' line item with calculation '1' and aggregation 'Average, Time: Sum'
Then apply the following calculation to the Average Receivables line item with aggregation 'Formula':
(Opening Receivables + 'Closing Receivables (Time Sum)') / (Time Aggregation + 1)
I am hoping that someone can suggest a simpler way to achieve this? My particular concern is that for every closing balance that I need an average for, I will need to create 2 additional line items to feed into the average calc (the Time Aggregation line item can be shared).
Answers

Unfortunately, I don't think that MovingSum is an option as if you use it in the formula, you cannot then set the time aggregation setting so 'formula'. And if you have anything else as the time aggregation setting, you don't get the correct quarterly/FY values.
0 
First, I agree with @andrewtye in that you should be using MovingSum instead of TimeSum as using TimeSum with a Time list can cause performance issues.
https://help.anaplan.com/13d8b5514e13413ba39ff25579d21e38TIMESUM
Secondly, have you considered using the Ratio as a summary option?
0 
@OliverMunns  Hi  how did you get on with this? We are trying to do the same so just wondered if you had made any progress on it. Also did your method work when doing YTD averages  I think it would but wanted to check if you had experience of it.
0 
@MattHarvey Hi, we're still using the same method for Average Receivables and other balance sheet accounts. I think the complexity on this is that for months we want a 2point average, for quarters a 4point and for FY a 13point. Would love a simpler solution but haven't found it yet.
For YTD Average Receivables we did find something simpler. This is were we want:
Jan 2point average
Feb 3point
Mar 4point
Q1 4 point
Apr 5point
…
Q2 7point
…
FY 13point
For this we've used:
(MOVINGSUM(Closing Receivables, YEARTODATE(1) + 1, 0) + LAG(Opening Receivables, YEARTODATE(1)  1, 0)) / (YEARTODATE(1) + 1)
Time summary = closing balance
Hope that this is helpful. Let me know if you find anything on Average Receivables
0 
Ah got it  thanks. Will let you know if we find anything else.
0