Get value for approximate time

[Anonymous]

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

[daniel79 · Accepted solution]

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.

[Anonymous]

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