cancel
Showing results for
Did you mean:
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
 Year Speed 1990 5 1990 10 1991 15 1991 20 1992 25 1992 30 1993 35 1993 40 1994 45 1994 50 1995 55 1995 60

Expected Results

 Year 5 Years Rolling Average of Max Speed 1990 10 1991 15 1992 20 1993 25 1994 30 1995 40
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: 5 Years Rolling Average of Max Speed

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

7 REPLIES 7
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

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 (
FILTER (
ALL ( Table1 );
Table1[Year] > ( _CurrentYear - _NrOfYears )
&& Table1[Year] <= MAX ( Table1[Year] )
);
"YearMax"; CALCULATE ( MAX ( Table1[Speed] ); ALL ( Table1[Speed] ) )
);
[YearMax]
)```

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

 1990 5 1990 10 1991 15 1991 20 1992 30 1993 40 1994 50 1995 40 1995 55 1995 60

Results

 Year 5 years rolling Max speed 1990 10 1991 15 1992 18 1993 21.67 1994 25.71 1995 42.5

Though my expected results are the same as below.

 Year 5 years rolling Max speed 1990 10 1991 15 1992 20 1993 25 1994 30 1995 40

BR,

Fahid

Highlighted
Super User

## Re: 5 Years Rolling Average of Max Speed

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

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

BR,

Fahid

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

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

Announcements

#### 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

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 39 members 1,093 guests
Recent signins: