Forum Discussion
Moving average based on prior N rows
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:
The comma goes before the All(). Try doing just All('Normalized Calendar') instead (remove the column reference).
Regards,
Pat
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.?
12 Replies
- mahoneypat
Microsoft Employee
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
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
- mahoneypat
The comma goes before the All(). Try doing just All('Normalized Calendar') instead (remove the column reference).
Regards,
Pat
- nandukrishnavs
Community Champion
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.
- nandukrishnavs
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 🙂
