cancel
Showing results for
Did you mean:
Regular Visitor

## Convert Rolling Average of Last 5 Years Measure to Calculated column

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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: Covert Rolling Average of Last 5 Years Measure to Calculated column

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 )
)
)```
2 REPLIES 2
Highlighted
Super User

## Re: Covert Rolling Average of Last 5 Years Measure to Calculated column

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 )
)
)```
Regular Visitor

## Re: Covert Rolling Average of Last 5 Years Measure to Calculated column

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.