cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fahid Regular Visitor
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

YearSpeedAreaExpected calculated column = Rolling Avg of EU Speed for Last 5 Years
19875EU7.5
198710EU7.5
198715Asia 
198820EU16.25
198825Asia 
198830EU16.25
198935Asia 
198940EU21
198945Asia 
199050EU30.71
199055Asia 
199060EU30.71
199165Asia 
199170EU40
199175EU40
199280EU48.18
199285Asia 
199290EU48.18
199395EU64.55
1993100EU64.55
1993105Asia 
1994110EU80.91
1994115Asia 
1994120EU80.91
1995125Asia 
1995130EU89.09
1995135Asia 
1996140EU105.45
1996145Asia 
1996150EU105.45
1997155EU117
1998160EU128.89
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
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
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 )
        )
    )
Fahid Regular Visitor
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.

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

 

Thank you so much.

 

BR,

Fahid