How to check the date value of one table date column is in between the 2 row values of another table

[sreedhary]

Hi, I am struggling with the following issue in Power BI desktop. My requirement is, how to check the Date Time value in Table A is in between the Sighting Date time values in Table B, if it is in between then show the minimum date/time record from Table B along with Table A record. In the below example, the record value 3/5/2020 3:48:33 PM from Table A is in between the date time values which are in 2 rows (3/4/2020 4:33:00 PM and 3/6/2020 5:33:00 AM) in Table B. In this show the minimum date-time record from Table B along with Table A row. I am in need of your helpo how to achieve this in Power BI desktop. The expected results is shown below.
Thank you for your help.

Table A:

Car Number	Date Time	Event Type
ACTX023293	3/5/2020 3:48:33 PM	Shock

Table B:

Car Number	Sighting Date Time	Indicator
ACTX023293	3/4/2020 4:33:00 PM	L
ACTX023293	3/6/2020 5:34:00 AM	L
ACTX023293	3/6/2020 7:44:15 AM	L

Expected Result:

Car Number	Date Time	Event Type	Sighting Date Time	Indicator
ACTX023293	3/5/2020 3:48:33 PM	Shock	3/4/202 4:33:00 PM	L

[Ashish_Mathur]

Hi,

These calculated column formulas work in Table1

Sighting Date Time = CALCULATE(Max(Table2[Sighting Date Time]),FILTER(Table2,Table2[Car Number]=EARLIER(Table1[Car Number])&&Table2[Sighting Date Time]<=EARLIER(Table1[Date Time])))

=LOOKUPVALUE(Table2[Indicator],Table2[Car Number],Table1[Car Number],Table2[Sighting Date Time],Table1[Sighting Date Time])

Hope this helps.

[sreedhary]

Ashish, Thank you for the calculations. I implemented these and testing the report  now. I will let keep you posted the updates.

Again, appreciate for your help.

[sreedhary]

Ashish,  The solution you propsed worked. Thank you so much for your help.

Thanks,

Sreedhar

[Ashish_Mathur]

You are welcome.

[V-pazhen-msft]

@sreedhary 

Try create the following two columns in Table A.

Sighting Date Time 1 = CALCULATE(MAX('Table B'[Sighting Date Time]),FILTER('Table B',[Sighting Date Time]<'Table A'[DateTime]&&'Table B'[Car Number]='Table A'[Car Number]))

Indication 1 = CALCULATE(MAX('Table B'[Indication]),FILTER('Table B',[Sighting Date Time]<'Table A'[DateTime] &&'Table B'[Car Number]='Table A'[Car Number]))

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sreedhary]

Paul, Thank you for the formulas. I implemented these and testing the report. I will let you know if there are any issues.

Again, appreciate for your help.

[amitchandak]

@sreedhary , New column in Table 1

New column = minx(filter(Table2,Table1[Car Number] =Table2[Car Number] && Table1[Date Time] >Table2[Date Time] ),Tabl22[Date Time])

[sreedhary]

Amit, Thank you for the response. But I am getting error when I use the suggested expression.

"Query(5,5) A single  value for a column 'Car Number' in table Table A can not be detemined..."

Moreover, I need to check the Table A date column value is in between the Table B Date column values (note: these spanned 2 rows).

Hope I am clear. Please let me know.

Pleasae advise.