Lookup between two dates

[Anonymous]

Hi All,

I have two tables/queries (snippet below) and what I am trying to do is match the driver from the table/query 1 to table/query 2 based on the following criteria - Car (Table2) matches Car (Table1) and Report Date (Table2) falls inbetween Commence date (Table1) and End Date (Table1)

Table/Query1

Car	Driver	Commence Date	End Date
Nissan	Will	01/01/2018 15:06	01/04/2018 10:00
Nissan	Charlie	01/04/2018 10:00	02/05/2018 09:00
Fiat	Peter	25/03/2018 00:00	31/04/2018 11:54
Mazda	Jan	03/02/2018 13:43	04/05/2018 09:36
Mazda	Jan	04/05/2018 09:36	21/05/2018 11:00
Mazda	Ben	21/05/2018 11:00	09/06/2018 13:43

Table/Query2

Car	Report Date
Nissan	31/04/2018 11:33
Fiat	25/03/2018 00:00
Mazda	07/05/2018 13:24

Therefore, for the above you would finish with the below

Car	Report Date	Driver
Nissan	31/04/2018 11:33	Charlie
Fiat	25/03/2018 00:00	Peter
Mazda	07/05/2018 13:24	Ben

Does anyone have any ideas?

Many Thanks,

Jack

[LivioLanzo · Accepted solution]

Are you looking to do it with a calculated table?

Table =
ADDCOLUMNS (
    Table2,
    "Driver",
    VAR tCar = Table2[Car]
    VAR Dte = Table2[Report Date]
    RETURN
        CALCULATE (
            VALUES ( Table1[Driver] ),
            Table1[Car] = tCar,
            Table1[Commence Date] <= dte,
            Table1[End Date] >= Dte
        )
)