Forum Discussion
Average basis hour duration from timestamp
Hi All
I have data with timestamp like below with single instance for each hour of day/date and i want to calculate average value for last 40 hours for each instance/hour. Any helps will be appreciated.
| Timestamp | Value |
| 1/1/2018 0:00 | 9621 |
| 1/1/2018 1:00 | 7240 |
| 1/1/2018 2:00 | 1826 |
| 1/1/2018 3:00 | 1722 |
| 1/1/2018 4:00 | 4110 |
| 1/1/2018 5:00 | 3745 |
| 1/1/2018 6:00 | 4316 |
| 1/1/2018 7:00 | 4479 |
| 1/1/2018 8:00 | 3008 |
| 1/1/2018 9:00 | 2626 |
| 1/1/2018 10:00 | 8620 |
| 1/1/2018 11:00 | 1571 |
| 1/1/2018 12:00 | 1607 |
| 1/1/2018 13:00 | 298 |
| 1/1/2018 14:00 | 3629 |
| 1/1/2018 15:00 | 9757 |
| 1/1/2018 16:00 | 7289 |
| 1/1/2018 17:00 | 105 |
| 1/1/2018 18:00 | 8406 |
| 1/1/2018 19:00 | 1756 |
| 1/1/2018 20:00 | 2217 |
| 1/1/2018 21:00 | 7859 |
| 1/1/2018 22:00 | 1101 |
| 1/1/2018 23:00 | 9790 |
| 2/1/2018 0:00 | 8777 |
| 2/1/2018 1:00 | 8141 |
| 2/1/2018 2:00 | 9291 |
| 2/1/2018 3:00 | 9015 |
| 2/1/2018 4:00 | 9150 |
| 2/1/2018 5:00 | 2421 |
| 2/1/2018 6:00 | 3708 |
| 2/1/2018 7:00 | 1216 |
| 2/1/2018 8:00 | 2443 |
| 2/1/2018 9:00 | 6619 |
| 2/1/2018 10:00 | 3490 |
| 2/1/2018 11:00 | 9510 |
| 2/1/2018 12:00 | 3688 |
| 2/1/2018 13:00 | 5479 |
| 2/1/2018 14:00 | 829 |
| 2/1/2018 15:00 | 8393 |
| 2/1/2018 16:00 | 6392 |
| 2/1/2018 17:00 | 825 |
| 2/1/2018 18:00 | 5675 |
| 2/1/2018 19:00 | 5752 |
| 2/1/2018 20:00 | 695 |
| 2/1/2018 21:00 | 1950 |
| 2/1/2018 22:00 | 8079 |
| 2/1/2018 23:00 | 460 |
| 3/1/2018 0:00 | 9310 |
Took me longer than I would like to admit 😛 Anyway, I solved it, by creating a calculated column with the following formula:
Last48Hours = AVERAGEX(FILTER(Table1, Table1[Timestamp] <= EARLIER(Table1[Timestamp]) && Table1[Timestamp] > EARLIER(Table1[Timestamp])-40/24), Table1[Value])The reason it took me longer than I expected was both 'how to add hours to a datetime' and also the double filter. Anyway, this will result in the following table:
This was fun, hope it helped 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
3 Replies
Hi dynamic2019,
You could try to refer to my sample to see whether it work or not.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Took me longer than I would like to admit 😛 Anyway, I solved it, by creating a calculated column with the following formula:
Last48Hours = AVERAGEX(FILTER(Table1, Table1[Timestamp] <= EARLIER(Table1[Timestamp]) && Table1[Timestamp] > EARLIER(Table1[Timestamp])-40/24), Table1[Value])The reason it took me longer than I expected was both 'how to add hours to a datetime' and also the double filter. Anyway, this will result in the following table:
This was fun, hope it helped 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
