How to take Average of Prior Year Values

dsulliv8
dsulliv8
in Modeling

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,

      Dpsullivan

Tagged:

Best Answer

  • ChrisAHeathcote
    ChrisAHeathcote
    Answer ✓

    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.

Comments

  • ChrisAHeathcote
    ChrisAHeathcote

    @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
    dsulliv8

    @ChrisAHeathcote 

     

     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?

  • ChrisAHeathcote
    ChrisAHeathcote

    @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]

     

Categories