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

tinathom

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

TristanS

@tinathom I would do the following:

1 create a custom months list

List name: Custom Month

List items

• Jan
• Feb
• Mar
• …
• Dec

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

• Jan 2020 map to Jan list item in custom month list
• Feb 2020 map to Feb list item in custom month list
• Jan 2021map to Jan list item in custom month list
• Feb 2021 map to Feb list item in custom month list
• Jan 2022 map to Jan list item in custom month list
• Etc

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]

ShubhamCh

Hi @tinathom

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!