How to take Average of Prior Year Values


Hi Anaplan Community,


    I am currently using the formula below to take a moving average of my prior year value. So for instance if this formula is in my April 21 month, it is summing April 20 to April 21 and taking the average of that. I want to adjust the formula to look at just the full Prior Year Jan 20 to Dec 20 and apply that average to each month in 21, so basically I want to take out the moving sum piece and just have it be a stagnant prior year sum & average. Any idea how to do this? I tried using the previous function, but couldn't get it to work.. Is there a function I can use to do this or do I need to create an IF THEN statement using my timesettings to get at prior year? Appreciate the help!


MOVINGSUM(value, -12, 0, AVERAGE)




Best Answer

  • ChrisAHeathcote

    An alternative is to use OFFSET() in the new line item ( I would rename it as 'Prior Year Value') to pull through the value as per 12 months prior.

    This approach would not require you to change or add any line items to the time month systems module.

    Therefore, you can use the YEARVALUE() against the Prior Year Value line item and return the full year average. 

    Both options should yield the same outcome.


  • @dsulliv8 

    Create a new line for the target line item. 

    Change the summary method to AVERAGE.

    In the target line item reference this new line item using YEARVALUE(New Line Item)

    The line item will return the average summary for the FY.

  • dsulliv8



     Thanks for your response! If I use the YEARVALUE(line item) formula that works, but it gives me the year value for the current year, any idea how to make it reference the prior year?

  • @dsulliv8 

    Of course.

    In that case you will need to use a LOOKUP function. 

    Do you have a time month systems module?

    Create a new module dimensioned my month only. 

    Create a two line items, both formatted as YEAR.

    Call them Current FY and Prior FY.

    In Current use =PERIOD(ITEM(TIME))

    In Prior FY=Current FY - 1

    Use the Prior FY in your LOOKUP to return the PY Average.

    The formula should look somthing like =AverageLineItem[LOOKUP:SystemTimeMonthModule.Prior FY]