Convert Rolling Average of Last 5 Years Measure to Calculated column

[Fahid]

Hello,

Based on below demo data, I need to make Rolling Average of EU Speed for Last 5 Years Calculated column. Rolling Average of EU Speed for Last 5 Years Calculated measure formula is:

Rolling Average of EU Speed for Last 5 Years Calculated measure =

VAR _CurrentYear = MAX(Demo[Year])

VAR _NumberofYears = 5

VAR _ReleventYears = FILTER(ALLSELECTED(Demo), Demo[Year]>=_CurrentYear-_NumberofYears && Demo[Year]<=_CurrentYear)

RETURN

CALCULATE(AVERAGE(Demo[Speed]), _ReleventYears, Demo[Area]="EU")

Can someone please help me to make Rolling Average of EU Speed for Last 5 Years Calculated column? Thank a lot for your help.

BR,

Fahid

Year	Speed	Area	Expected calculated column = Rolling Avg of EU Speed for Last 5 Years
1987	5	EU	7.5
1987	10	EU	7.5
1987	15	Asia
1988	20	EU	16.25
1988	25	Asia
1988	30	EU	16.25
1989	35	Asia
1989	40	EU	21
1989	45	Asia
1990	50	EU	30.71
1990	55	Asia
1990	60	EU	30.71
1991	65	Asia
1991	70	EU	40
1991	75	EU	40
1992	80	EU	48.18
1992	85	Asia
1992	90	EU	48.18
1993	95	EU	64.55
1993	100	EU	64.55
1993	105	Asia
1994	110	EU	80.91
1994	115	Asia
1994	120	EU	80.91
1995	125	Asia
1995	130	EU	89.09
1995	135	Asia
1996	140	EU	105.45
1996	145	Asia
1996	150	EU	105.45
1997	155	EU	117
1998	160	EU	128.89

[AlB · Accepted solution]

Hi @Fahid 

Try this for your calculated column:

NewColumn = 
VAR _NumberofYears = 5
RETURN
    IF (
        Table1[Area] = "EU";
        CALCULATE (
            AVERAGE ( Table1[Speed] );
            Table1[Year] >= EARLIER ( Table1[Year] ) - _NumberofYears && Table1[Year] <= EARLIER ( Table1[Year] );
            Table1[Area] = "EU";
            ALL ( Table1 )
        )
    )

[Fahid]

Hey @AlB,

Sorry for late response. Your solution is perfect for achieving results in one Table.

Could you please help me to achieve similar results in connected Tables? I mean the main data is in Table1 and I would like to find similar results in Table2. Please see pbix file for more clarification.

https://drive.google.com/file/d/1iBsx5Kk1IGtBPMdJ84FFuvwc2tB034hN/view?usp=sharing

Thank you so much.

BR,

Fahid