Calculate Average from the earliest Fy year to later Fy year (excluding current FY year)

[CK_WAKE]

Hi there,

I have a table as shown below, and I would like to calculate the average by dimension starting from the beginning of the earliest financial year to the last completed financial year (excluding the current financial year). An example is provided below.

Thank you!

Accessory	FY20	FY21	FY22	FY23	3 Year Avg. (excl FY23)
Desktop	10	20	17	1	16
Laptop	45	29	18	11	31
Mobile	76	70	29	14	58

[TauseefKhan]

Hi @CK_WAKE,

Result:

Column_1:
=ValueList('Desktop','Laptop','Mobile')

Column_2:
IF(ValueList('Desktop','Laptop','Mobile') = 'Desktop',
ONLY({<Accessory={'Desktop'}>} FY20),
IF(ValueList('Desktop','Laptop','Mobile') = 'Laptop',
ONLY({<Accessory={'Laptop'}>} FY20),
IF(ValueList('Desktop','Laptop','Mobile') = 'Mobile',
ONLY({<Accessory={'Mobile'}>} FY20),
NULL()
)
)
)

vFY2:
IF(ValueList('Desktop','Laptop','Mobile') = 'Desktop',
ONLY({<Accessory={'Desktop'}>} FY21),
IF(ValueList('Desktop','Laptop','Mobile') = 'Laptop',
ONLY({<Accessory={'Laptop'}>} FY21),
IF(ValueList('Desktop','Laptop','Mobile') = 'Mobile',
ONLY({<Accessory={'Mobile'}>} FY21),
NULL()
)
)
)

Column_3:

Change FY20 to FY21 for vFY21 and so on. (You can create variables for each.)

Result Column - 3 Year Avg. (excluding FY23):

=Ceil(($(vFY20) + $(vFY21) + $(vFY22))/3)


******Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.********