I have a time-series dataset of tempratures that is granular to the second. However, not every second or minute necessarily has a data point. I am trying to create a time-weighted average of the temperature over every hour. The data set includes a timestamp for every hour on the hour (12:00:00 AM, 1:00:00 AM, etc), and I have also added the "hour" value for each timestamp for use as an axis.
| tag |
time |
value |
Actual Hour |
| T1 |
11/18/2021 0:00 |
68.3984604 |
11/18/2021 0:00 |
| T1 |
11/18/2021 0:52 |
68.13246155 |
11/18/2021 0:00 |
| T1 |
11/18/2021 1:00 |
68.044014 |
11/18/2021 1:00 |
| T1 |
11/18/2021 2:00 |
67.3130112 |
11/18/2021 2:00 |
| T1 |
11/18/2021 3:00 |
66.5820084 |
11/18/2021 3:00 |
| T1 |
11/18/2021 3:22 |
66.30706787 |
11/18/2021 3:00 |
| T1 |
11/18/2021 4:00 |
65.9167786 |
11/18/2021 4:00 |
| T1 |
11/18/2021 4:47 |
65.42238617 |
11/18/2021 4:00 |
| T1 |
11/18/2021 5:00 |
65.1847687 |
11/18/2021 5:00 |
| T1 |
11/18/2021 6:00 |
64.0516281 |
11/18/2021 6:00 |
| T1 |
11/18/2021 6:53 |
63.04325104 |
11/18/2021 6:00 |
| T1 |
11/18/2021 7:00 |
62.9881592 |
11/18/2021 7:00 |
| T1 |
11/18/2021 7:24 |
62.78230286 |
11/18/2021 7:00 |
| T1 |
11/18/2021 8:00 |
63.3581505 |
11/18/2021 8:00 |
| T1 |
11/18/2021 8:18 |
63.65802765 |
11/18/2021 8:00 |
| T1 |
11/18/2021 8:22 |
65.37107849 |
11/18/2021 8:00 |
| T1 |
11/18/2021 8:40 |
65.05717468 |
11/18/2021 8:00 |
| T1 |
11/18/2021 9:00 |
66.4762268 |
11/18/2021 9:00 |
The time-weighted average I am looking for is basically how much of the hour was at a particular temperature, and that portion of the hour contributed a percentage of the temperature to the average. So if there were timestamps of 0:00 @ 50, 0:20 @52 and 1:00 @54, the weighted average would be (1/3)*50 + (2/3)* 52 = 51.33
To do this I created a calculated column to find the time-weighting of each row.
Time Weight =
VAR __ThisTime = [time]
VAR __ThisTrain = [tag]
VAR __ThisActHour = [Actual Hour]
VAR __ThisHourVals =
FILTER ( Temps, Temps[tag] = __ThisTrain && Temps[Actual Hour] = __ThisActHour )
VAR __NextTime1 =
MINX ( FILTER ( __ThisHourVals, [time] > __ThisTime ), [time] )
VAR __NextTime =
IF (
ISBLANK ( __NextTime1 ),
Temps[Actual Hour] + ( 1 / 24 ),
IF (
HOUR ( __NextTime1 ) = HOUR ( __ThisTime ),
__NextTime1,
Temps[Actual Hour] + ( 1 / 24 )
)
)
VAR __Diff =
DATEDIFF ( __ThisTime, __NextTime, SECOND )
VAR __Wt =
DIVIDE ( __Diff, 3600 )
RETURN
__Wt
then the weighted average would simply be
TWAvgTemp =
SUMX ( Temps, Temps[Time Weight] * Temps[value] )
The formula and measure work just fine, but to me the solution is not elegant (and probably would not work if I were to time-weight over a different period of time). Has anyone else had to do this and come up with a more elegant/scalable solution?
Thanks
David
