topic Re: Lookup value based on start and end date conditions from another table in DAX Commands and Tips

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

rubentj — Wed, 28 Sep 2022 17:35:23 GMT

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

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

Jihwan_Kim — Wed, 28 Sep 2022 18:51:55 GMT

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 )

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

CNENFRNL — Wed, 28 Sep 2022 23:40:39 GMT

A proper model facilitate possible analyses and calculations.

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

rubentj — Thu, 29 Sep 2022 07:16:15 GMT

Thank you so much!