Average of previous year and current year

logikalyan · 2024-11-01T12:19:22+00:00

There was an error rendering this rich post.

logikalyan

Hello community,

I need to calculate average of previous year end (e.g FY23) and current year end (e.g. FY24).

Logic should be FY24 + FY23 / 2.

How can I return these vales from a line item which has both FY23 and FY24 numbers.

Any ideas much appreciated.

Thanks

Kal

Accepted answers

Prajjwal88

Hi @logikalyan ,

The Solution given by @seymatas1 seems correct to me, although just to add if your monthly summary is set to Sum and you want to get the same average value by the formula you suggested you can simply make two lookup line items in a system lookup module for both year i.e., FY23 and FY24 and then simply pull the amount line item values using the lookup itself to another line item and further use it.
You may take help from example mentioned below for developing the same,

Hope this helps.

Prajjwal88

@logikalyan , I hadn't hardcoded the same line items as the you must be having any current date or current month line item in your model so you can simple use that to build a formula for these.
For example i have used current date over here,

And for FY23(Previous Year) you can simple write formula as: 'FY24' - 1

Hope this helps.

All comments

seymatas1

Hi Kal,

You can select Average time summary if you have only FY23 and FY24 in your time dimension.

Also, turn on Total of All Periods if you use model calendar, and Total of All if you use time ranges. Turning on Total of All Periods in model calendar increases the number of calculations. So be mindful before enabling it for just one module. Using an FY23 - FY24 time range can be a better option in your case.

I hope this helps.

Seyma 🌷🙂

seymatas1

Selecting Average summary method calculates averages for all time periods.

For example, if you have months, your FY23 will be the average of all your months.

If your month summary is set to Sum, and you want to average only years, there is no native solution for that, you will need to develop it.

I hope this helps.

Seyma 🌷🙂

logikalyan

Thanks for your response Seyma,

I understand what you are saying but I might not put my question correctly. Below is my module.

I have values in the first line item. In the second line item I need a formula to get FY23 number and FY24 number to get a average of both.

How can I get FY23 number? Does anaplan have a formula to pull previous year number?

Thanks

Kal

Prajjwal88

Hope this helps.

logikalyan

Hi Prajj,

You solution make absolute sense however in there a way I can achieve this without hardcoding FY23 and 24. The reason is because that formula will not work in future when current year becomes FY24 unless we manually change the formula to include years.

Thanks

Kal

Prajjwal88

And for FY23(Previous Year) you can simple write formula as: 'FY24' - 1

Hope this helps.

seymatas1

@Prajjwal88 's solution works perfectly. There is another solution here with summary methods and an extra line item.

You can also use TIMESUM instead of SELECT if All Periods is not open in your time range or model calendar.

logikalyan

Thanks all,

I will try these on Monday and let you know how I am getting on.

Kal

logikalyan

Hi Prajj,

With bit of your logic and followed by my logic, I have successfully completed my work.

Thank you so much

Kal

Prajjwal88

@logikalyan, happy to help :)

Quick Links

Can you help?

Unanswered questions

Action to copy data from one version to another version only for a specific module
Ideally, I would like to setup an action that can copy data from one version to another only for a specific module. For every new version I create, I import new data into certain modules and then I manually copy data over for a specific module. I am wondering if I can just setup an action to select an origin version,…
Where can I find export file after running export process using machine task in a workflow?
I am testing export actions using workflow but its not generating any file when I am running the workflow, but the same process is generating export file when ran from the Actions→Process section. Is their any issue with the workflow functionality? Also, If I want to generate a file after completion of workflow and pull…
Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…