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,

Dpsullivan

Accepted answers

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.

All comments

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

@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

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

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.

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…