Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to calculate a moving average based on the prior N rows of a table. Something that would match the table below.
I have tried the following but it does not provide the desired result:
Solved! Go to Solution.
The comma goes before the All(). Try doing just All('Normalized Calendar') instead (remove the column reference).
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@StephaneMA colum type is whole number/decimal. Can you copy the table which i shared above and create the same measure (not calculated column) without any edits. Also share the snapshot.?
Try this DAX measure.
Moving Avg =
VAR n = 5
VAR current =
SELECTEDVALUE ( Table[weeknumber] )
VAR avg =
CALCULATE (
AVERAGE ( Table[Subs] ),
FILTER (
ALL ( Table[weeknumber] ),
Table[weeknumber] <= current
&& Table[weeknumber] >= current - n
)
)
RETURN
avg
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Thank you nandukrishnavs,
I appreciate the suggestion. I get exactly the same result than I did with Pat's suggestion above.
Weeknumber | Subs |
1 | 282 |
2 | 508 |
3 | 540 |
4 | 518 |
5 | 717 |
6 | 599 |
7 | 622 |
8 | 416 |
9 | 603 |
10 | 622 |
11 | 730 |
12 | 617 |
13 | 618 |
Moving Avg =
VAR n = 5
VAR _selectedweekno =
SELECTEDVALUE ( 'Table'[Weeknumber] )
VAR result =
CALCULATE (
AVERAGE ( 'Table'[Subs] ),
FILTER (
ALL ( 'Table'[Weeknumber] ),
'Table'[Weeknumber] <= _selectedweekno
&& 'Table'[Weeknumber] >= _selectedweekno - n
)
)
RETURN
IF(_selectedweekno>=n,result,BLANK())
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
I appreciate the help but I do not get the same result at all. I created a new table with the same data to replicate your formula but I get bery different results....
I am not sure at this point what I am doing wrong to get this result.
@StephaneMA colum type is whole number/decimal. Can you copy the table which i shared above and create the same measure (not calculated column) without any edits. Also share the snapshot.?
That did it.......!
Thank you nandukrishnavs.
There must be something wromng with the original dataset. I will fix that issue now that I have a working average working!
I really appreciate your assistance.
Prior 5 Weeks =
VAR currentweeknumber =
SELECTEDVALUE ( Table[Normalized Week Number] )
RETURN
CALCULATE (
AVERAGE ( Table[Subs])
ALL ( Table[Normalized Week Number] ),
Table[Normalized Week Number] <= currentweeknumber,
Table[Normalized Week Number] >= currentweeknumber - 5
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you Pat,
I appreciate the quick reply.
When I enter the code as sent, I get the following:
If I add a coma before the ALL function, the error disappears but the result is not what I expect (see below)
Can you think of what is creating the issue?
Again, I really appreciate your assistance.
SL
The comma goes before the All(). Try doing just All('Normalized Calendar') instead (remove the column reference).
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Pat,
thank you for the help......you had it all along....there must be something wrong with the original dataset which affected the result.
I appreciate your assistance!
Thank you again
Sorry Pat,
Gave the same result
Well that's unexpected (at least to me). Numbers look right beyond Week 5. Are there Opportunity data raws before Normalized Week 1? I will take a long again tomorrow, if someone else doesn't solve it first.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.