Lookup value based on start and end date conditions from another table

[rubentj]

Hi. I want to calculate a column if an instance is true. 

I have two tables. Table one where I have a date for each date a car is at a location.

CAR ID	Location	Date
1	A	1/1/2020
1	A	2/1/2020
1	A	3/1/2020
1	A	4/1/2020
2	B	10/1/2020
2	B	11/1/2020
2	B	12/1/2020
3	B	10/2/2020
3	B	11/2/2020
3	B	12/2/2020

This shows that Car 1 has been on location A between 1/1/2020 and 4/1/2020, and so on.

Further I have a table showing the usage of these cars per rental agreements. 

Car ID	Start date	End date
1	1/1/2020	3/1/2020
2	10/1/2020	10/1/2020
2	11/1/2020	11/1/2020
3	10/2/2020	11/2/2020

Expected result:

CAR ID	Location	Date	Is used (1 = Yes / 0 = No
1	A	1/1/2020	1
1	A	2/1/2020	1
1	A	3/1/2020	1
1	A	4/1/2020	0
2	B	10/1/2020	1
2	B	11/1/2020	1
2	B	12/1/2020	0
3	B	10/2/2020	1
3	B	11/2/2020	1
3	B	12/2/2020	0

So basically what I want is to calculate the column "Is Used" in Table 1 based on if the Date in Table 1 is between Start and End date in table 2 for some rows with Car ID matching. 

Anyone?

In advance, thank you so much. 

Best regards, 

Ruben

[CNENFRNL]

A proper model facilitate possible analyses and calculations.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

Is used CC =
IF (
    COUNTROWS (
        FILTER (
            Agreement,
            Agreement[Car ID] = Location[CAR ID]
                && Agreement[Start date] <= Location[Date]
                && Agreement[End date] >= Location[Date]
        )
    ) = 0,
    0,
    1
)

[rubentj]

Thank you so much!