How to find the average of the same month for different years?

I have 5 years of data. I need to find the average of the same month for the 5 years.
For example,

(Jan 19+jan 20+ jan 21+ jan 22 + jan 23) /5. Similarly i need to find for feb, march, April…

I used month and model calendar for the time dimension.
How do i develop this in anaplan?
thank you

Accepted answers

@tinathom I would do the following:

1 create a custom months list

List name: Custom Month

List items

2 create a months time period system module that maps each month period to its corresponding custom month. Eg

module name: SysMonths

dimensions: time period months

Line item: custom month mapping

3 create a module to calculate the ave. Eg

module name: calc

Dimensions: custom month, <other dimensions used by source module for average>

line item: calc ave

Formula: <source module>.<source line>[AVERAGE: SysMonths.custom month mapping]

Let's say, you want to calculate average of 'Your Line Item' for a month in different years.

Jan 18 = Jan 19 + Jan 20 + ……. + Jan 23

Simply use Lead function

(LEAD(Your Line Item,12,0) +LEAD(Your Line Item,24,0)+ LEAD(Your Line Item,36,0) +LEAD(Your Line Item,48,0)+LEAD(Your Line Item,60,0))/5

if you want to do

Jan 18 = Jan 18 + Jan 19 + ……. + Jan 22

(LEAD(Your Line Item,12,0) +LEAD(Your Line Item,24,0)+ LEAD(Your Line Item,36,0) +LEAD(Your Line Item,48,0)+ Your Line Item )/5

This can be done using offset and lookup functions as well.

Thanks!

Sorting General List
Hi, I wonder if there's a way I can sort my general list alphabetically. The list is used as a drop down menu in a line item module and I want the drop down menu to be easy to navigate. I've already tried the Order List action. I already have a module with a text-formatted line item based on that list. But when I try to…
Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…