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 further clarification.
Could anyone point me to the right direction please?
Table 1
| Date | Time | Item | Table 2 Match |
| 01/01/2022 | 01:30 | BB4 | 200 |
| 01/01/2022 | 01:25 | AA4 | 300 |
| 01/01/2022 | 02:35 | BB3 | 425 (average) |
Table 2
| Date | Time | Item | Value |
| 01/01/2022 | 01:25 | BB4 | 200 |
| 01/01/2022 | 01:25 | AA4 | 300 |
| 01/01/2022 | 02:40 | BB3 | 450 |
| 01/01/2022 | 02:15 | BB3 | 400 |
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.
2 Replies
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.