Forum Discussion
Get value for approximate time
Hi there, I am looking to find a solution (preferably a calculated column) that will find a value of an item or an average for the closest time in Table 2 (+- 20 minutes). See example below for ...
Here's my take on this.
I first get the closest datetime for the same item and then average the values for the closest records with a tolerance of 20 mins.
Measure
Avg Closest = VAR vMAXTOL = 20 VAR vDatetime = min(TABLE1[Datetime]) VAR vClosest = CALCULATE(MINX(TABLE2,abs(TABLE2[Datetime2]-vDatetime)*24*60)) VAR vAvg = AVERAGEX(filter(TABLE2,(abs(TABLE2[Datetime2]-vDatetime)*24*60)<=vClosest+0.00001 && vClosest<=vMAXTOL),TABLE2[Value]) return vAvgLink to data and pbix file.
Here's my take on this.
I first get the closest datetime for the same item and then average the values for the closest records with a tolerance of 20 mins.
Measure
Avg Closest =
VAR vMAXTOL = 20
VAR vDatetime = min(TABLE1[Datetime])
VAR vClosest = CALCULATE(MINX(TABLE2,abs(TABLE2[Datetime2]-vDatetime)*24*60))
VAR vAvg = AVERAGEX(filter(TABLE2,(abs(TABLE2[Datetime2]-vDatetime)*24*60)<=vClosest+0.00001 && vClosest<=vMAXTOL),TABLE2[Value])
return vAvg
Link to data and pbix file.
This is brilliant Daniel - work's like magin as a measure. Do you think it would work the same as a calculated column? I would like to visualise the data and it does not seem to be working that easily. Thanks