cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fahid Regular Visitor
Regular Visitor

5 Years Rolling Average of Max Speed

Hello everyone,

 

I need to calculate 5 Years Rolling Average of Max Speed. Demo data and expected results are given below.


I have used the below code to calculate 5 Years Rolling Average of Speed but couldn't convert it for 5 Years Rolling Average of Max Speed.

5 Years Rolling Average of Speed =
VAR __NrOfYears = 5
VAR __CurrentYear = MAX(Table1[Year])
VAR __RelevantYears = FILTER(ALL('Table1'), (Table1[Year]<=__CurrentYear) && (Table1[Year]>(__CurrentYear-__NrOfYears)))
RETURN
CALCULATE(AVERAGE ('Table1'[Speed]), __RelevantYears)
 
Please help me to convert this code for 5 Years Rolling Average of Max Speed. Any other way to achieve my expected results is also appreciated. Thanks a lot.
 
Demo Data
YearSpeed
19905
199010
199115
199120
199225
199230
199335
199340
199445
199450
199555
199560

 

Expected Results

Year5 Years Rolling Average of Max Speed
199010
199115
199220
199325
199430
199540
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 5 Years Rolling Average of Max Speed

Measure_V2 = 
VAR _CurrentYear = MAX ( Table1[Year] )
VAR _NrOfYears = 5
RETURN
    AVERAGEX (
        ADDCOLUMNS (
            FILTER (
                ALL ( Table1[Year] );
                Table1[Year] > ( _CurrentYear - _NrOfYears )
                    && Table1[Year] <= MAX ( Table1[Year] )
            );
            "YearMax"; CALCULATE ( MAX ( Table1[Speed] ) )
        );
        [YearMax]
    )

 

7 REPLIES 7
Fahid Regular Visitor
Regular Visitor

Re: 5 Years Rolling Average of Max Speed

Hello @Stachu @AlB,

 

I have used your solutions to solve 5 years rolling average of speed. Could you please help to convert it for 5 years rolling average of Max speed? Thanks for your time.

 

BR,

Fahid

Highlighted
Super User
Super User

Re: 5 Years Rolling Average of Max Speed

Hi @Fahid 

 

Try this measure in a matrix visual with Table1[Year] in rows:

 

 

Measure =
VAR _CurrentYear = MAX ( Table1[Year] )
VAR _NrOfYears = 5
RETURN
    AVERAGEX (
        ADDCOLUMNS (
            FILTER (
                ALL ( Table1 );
                Table1[Year] > ( _CurrentYear - _NrOfYears )
                    && Table1[Year] <= MAX ( Table1[Year] )
            );
            "YearMax"; CALCULATE ( MAX ( Table1[Speed] ); ALL ( Table1[Speed] ) )
        );
        [YearMax]
    )

 

 

Fahid Regular Visitor
Regular Visitor

Re: 5 Years Rolling Average of Max Speed

Hey @AlB,

 

Thanks a lot for the reply. Your solution does give expected results for before given data. But in my original data, I also have some years where data is less or more per year. For example, for below-given data, I get different results. 

Year            Speed

19905
199010
199115
199120
199230
199340
199450
199540
199555
199560

 

Results

Year5 years rolling Max speed
199010
199115
199218
199321.67
199425.71
199542.5

 

Though my expected results are the same as below.

Year5 years rolling Max speed
199010
199115
199220
199325
199430
199540

 

Can you please help with this issue? Many thanks.

 

BR,

Fahid

 

Super User
Super User

Re: 5 Years Rolling Average of Max Speed

Measure_V2 = 
VAR _CurrentYear = MAX ( Table1[Year] )
VAR _NrOfYears = 5
RETURN
    AVERAGEX (
        ADDCOLUMNS (
            FILTER (
                ALL ( Table1[Year] );
                Table1[Year] > ( _CurrentYear - _NrOfYears )
                    && Table1[Year] <= MAX ( Table1[Year] )
            );
            "YearMax"; CALCULATE ( MAX ( Table1[Speed] ) )
        );
        [YearMax]
    )

 

Fahid Regular Visitor
Regular Visitor

Re: 5 Years Rolling Average of Max Speed

Hey @AlB,

 

Works perfectly. Thank you so much. Can you please suggest me any course or material to learn to code in Power BI as you do? You know you are the best here Smiley Very Happy

 

BR,

Fahid

Super User
Super User

Re: 5 Years Rolling Average of Max Speed

Hi @Fahid 

Books by some people that are around in this forum are pretty good:

 

@MattAllington's Superchage Power BI Really well explained, step by step. A very good option for beginners. Check out Matt's website as well.

 

@Phil_Seamark's Beginning DAX with Power BI is probably a good choice if you have a SQL background. It also has a lot of truly interesting coding examples.

 

Last, but certainly not least, the DAX bible by @marcorusso and @AlbertoFerrariThe Definitive Guide to DAX

Rigorous and thoroughgoing, it might at times be harder to read than other resources but it's the ultimate go-to book in my view. These guys just crush you with knowledge. Everything there's to know about DAX (ok, 99%), you will find here. Second edition only days away.

Plus there's also their SQLBI site, with a wealth of succulent material (articles, patterns, courses...)

 

 

Fahid Regular Visitor
Regular Visitor

Re: 5 Years Rolling Average of Max Speed

Hey @AlB

 

Thank you so much for everything. I will definitely follow your instructions.

 

BR,

Fahid 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 75 members 1,148 guests
Please welcome our newest community members: