Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Fahid
Helper I
Helper I

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

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]
    )

 

View solution in original post

7 REPLIES 7
Fahid
Helper I
Helper I

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

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]
    )

 

 

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

 

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]
    )

 

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 😄

 

BR,

Fahid

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...)

 

 

Hey @AlB

 

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

 

BR,

Fahid 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.