Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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 |
Solved! Go to 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] )
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
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 |
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 @AlbertoFerrari: The 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...)
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |