Average of previous year and current year

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

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

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.

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

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

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 :)