You can use the @AVGRANGE function to calculate rolling averages and the @ACCUM function to calculate rolling year-to-date values.
For example, assume that a database contains monthly Sales data values and that the database outline includes the members AVG_Sales and YTD_Sales.
You would add this formula to the AVG_Sales member:
@AVGRANGE(SKIPNONE, Sales, @CURRMBRRANGE(Year, LEV, 0, , 0));
And you would add this formula on the YTD_Sales member:
@ACCUM(Sales);
Essbase calculates the average Sales values across the months in the dimension tagged as time. The SKIPNONE parameter means that all values are included, even #MISSING values. Essbase places the results in AVG_Sales. See Consolidating #MISSING Values.
Table 62 shows the results when Essbase calculates the cumulative Sales values and places the results in YTD_Sales:
The values for AVG_Sales are averages of the months-to-date. For example, AVG_Sales -> Mar is an average of Sales for Jan, Feb, and Mar.
The values for YTD_Sales are the cumulative values up to the current month. So YTD_Sales -> Feb is the sum of Sales -> Jan and Sales -> Feb.