Join the closest datetime's value of one table to the datetime of another table

[JeffT]

Hi guys, 

I have an issue. There are 2 tables, table 1 has datetime and value, value can either be "on" or "off". Table 2 has datetime and value, value can either be "good" or "suspect". My challenge is I want to join table 2 to table 1 based on datetime, but the datetimes of each table do not match each other, I am trying to map the closest/previous datetime that matches the two tables and join them.

Note, the tables row count are not the same (one is longer than the other)

Ex.

table 1 

2022-01-02 8:29:08 PM	OFF
2022-01-06 10:23:06 AM	ON
2022-01-06 1:20:26 PM	OFF
2022-01-06 1:55:16 PM	OFF
2022-01-08 9:54:52 PM	ON

table 2

2022-01-01 9:48:19 AM	Good
2022-01-01 5:44:54 PM	Suspect
2022-01-02 1:46:30 AM	Suspect
2022-01-02 9:48:06 AM	Good
2022-01-02 5:44:41 PM	Suspect
2022-01-03 1:46:17 AM	Good
2022-01-03 9:47:53 AM	Suspect
2022-01-03 5:44:28 PM	Good

[JeffT]

Thank you so much for your help!!!!

[barritown]

Hi @JeffT,

If I have correctly understood the task, you need a calculated column like this:

Here is the same code in plain text for convenience:

Status = 
VAR CurrentTimestamp = [Timestamp]
VAR Timestamp_Tbl2 = MAXX ( FILTER ( Tbl2, Tbl2[Timestamp] <= CurrentTimestamp ), Tbl2[Timestamp] )
RETURN LOOKUPVALUE ( Tbl2[Status], Tbl2[Timestamp], Timestamp_Tbl2 )

 Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian