Forum Discussion
Moving Averages on non-date data
Hello experts.
I have a very simple table, but I'd like to find the moving average for the laxt 3 events. I see a lot of data for moving averages using dates, but Im struggling to get to the root of the expression for these event#s. Thanks in advance!
| Event# | RevCat1 |
| 294 | $0.09 |
| 295 | $0.05 |
| 296 | $0.11 |
| 297 | $0.08 |
| 298 | $0.04 |
| 299 | $0.07 |
| 300 | $0.02 |
| 301 | $0.03 |
| 302 | $0.04 |
| 303 | $0.03 |
| 304 | $0.08 |
| 305 | $0.08 |
| 306 | $0.19 |
| 307 | $0.10 |
| 308 | $0.09 |
| 309 | $0.15 |
| 310 | $0.12 |
Since you have a continuous range of numbers for your events, we can take the average of the RevCat1 column where the Event# is greater than the current row's Event# - 3 and less than or equal to the current row's Event#. In DAX for a new measure:
MovingAvg = CALCULATE(AVERAGE(TableName[RevCat1]), FILTER(ALL(TableName), TableName[Event#] > MAX(TableName[Event#) - 3), FILTER(ALL(TableName), TableName[Event#] <= MAX(TableName[Event#])))
2 Replies
Since you have a continuous range of numbers for your events, we can take the average of the RevCat1 column where the Event# is greater than the current row's Event# - 3 and less than or equal to the current row's Event#. In DAX for a new measure:
MovingAvg = CALCULATE(AVERAGE(TableName[RevCat1]), FILTER(ALL(TableName), TableName[Event#] > MAX(TableName[Event#) - 3), FILTER(ALL(TableName), TableName[Event#] <= MAX(TableName[Event#])))