Forum Discussion
How to calculate Moving Average based on a Rolling 10 hours
- 10 years ago
Just in case you missed this one:
http://www.daxpatterns.com/time-patterns/
Look at the Aggregation Pattern information as this should be basically what you need. Probably a little more complicated since you are working with time instead of date though. Probably will have to use EARLIER and the example from the EARLIER page might get you what you want if you modify the formula's FILTER to give you everything from the last 10 hours and use AVERAGE or AVERAGEX instead of COUNTROWS.
https://support.office.com/en-US/article/EARLIER-Function-DAX-90e5c2b0-50e2-417d-b4c7-7528febcef97
Scroll all the way to the end and read the explanation very carefully. Essentially, if your EARLIER grabbed the 3rd column value, you modified the filter to give you everything within the past 10 hours. Here is what I came up with in trying this:
MovingAverage = CALCULATE(AVERAGE([Value]),FILTER(MovingAverage, EARLIER([Time])>=[Time]))
Obviously, I haven't tweaked this to account for within 10 hours, on the same date, etc. but the results I came up with were:
MovingAverage
181.75722 162.2699 218.727775 223.8 276.6 252.9037 Same order as your rows. So, the earliest time value (6:05) gets 276.6, the average of its single row. The 6:16 value gets 223.8, the average of 276.6 and 171 and so on.
Is this what you are looking for basically?
One note, I imported your values into Desktop and I got rid of the single quotes and such so that the dates came in as dates, the time values came in as time values and the "value" column came in as a decimal number.
- 10 years ago
Happy to help, and for the record, EARLIER is a dumb name for that function IMHO considering how it is most often used. I would have gone with something like CURRENT.
Just in case you missed this one:
http://www.daxpatterns.com/time-patterns/
Look at the Aggregation Pattern information as this should be basically what you need. Probably a little more complicated since you are working with time instead of date though. Probably will have to use EARLIER and the example from the EARLIER page might get you what you want if you modify the formula's FILTER to give you everything from the last 10 hours and use AVERAGE or AVERAGEX instead of COUNTROWS.
https://support.office.com/en-US/article/EARLIER-Function-DAX-90e5c2b0-50e2-417d-b4c7-7528febcef97
Scroll all the way to the end and read the explanation very carefully. Essentially, if your EARLIER grabbed the 3rd column value, you modified the filter to give you everything within the past 10 hours. Here is what I came up with in trying this:
MovingAverage = CALCULATE(AVERAGE([Value]),FILTER(MovingAverage, EARLIER([Time])>=[Time]))
Obviously, I haven't tweaked this to account for within 10 hours, on the same date, etc. but the results I came up with were:
MovingAverage
| 181.75722 |
| 162.2699 |
| 218.727775 |
| 223.8 |
| 276.6 |
| 252.9037 |
Same order as your rows. So, the earliest time value (6:05) gets 276.6, the average of its single row. The 6:16 value gets 223.8, the average of 276.6 and 171 and so on.
Is this what you are looking for basically?
One note, I imported your values into Desktop and I got rid of the single quotes and such so that the dates came in as dates, the time values came in as time values and the "value" column came in as a decimal number.
- dbadmin10 years ago
Advocate V
THANK YOU!!!! I'm not very familiar with DAX at all - so I didn't even know to look for EARLIER. It works like a charm. Much simpler than what I was trying to come up with. :)
Thank you again so much!! I've been working on this one off and on for weeks now.
- Greg_Deckler10 years ago
Community Champion
Happy to help, and for the record, EARLIER is a dumb name for that function IMHO considering how it is most often used. I would have gone with something like CURRENT.