Average basis hour duration from timestamp

[Anonymous]

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

[dax]

Hi dynamic2019,

You could try to refer to my sample to see whether it work or not.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JarroVGIT · Accepted solution]

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 🙂

[Anonymous]

Both the answers work as absolute solution to my problem however accepting the first answer as solution for below 2 reasons -

1. ofcourse the first solution.

2. It works on existing timestamp column instead of additional helper column.

Tons of thanks to both! 🙂