Identifying matching dates by range between two tables

[Anonymous]

Hello - 

I need to identify matching employee id and date values between two tables. The first table is for Travel. 

TRAVEL

employee id	travel start date	transaction amount ($)	Desired New Column 'Match?'
25808	12/10/2021	739.52	yes
28727	12/28/2021	362.20	no
30787	12/30/2021	671.62	no
02471	1/1/2022	170.92	yes
10142	1/1/2022	405.98	yes

Second table is for training. 

TRAINING

employee id	on date
25808	12/10/2021
28727	3/9/2021
30787	4/30/2021
02471	12/31/2022
10142	1/2/2022

My goal is to identify what travel is training-related so that we can determine travel costs for training programs. To do this (I think) I need to identify matching employee id/travel start date and employee id/on date values and create a new column with yes/no to identify matches and allow me to filter non-training travel out. I'd like to count travel start dates that are one day before or after Training on date as a match.

I'm stuck.  Any help will be very much appreciated.

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

You can refer to the following calculated column/

Match =
VAR _predate = [travel start date] - 1
VAR _nextdate = [travel start date] + 1
VAR _values = { _predate, _nextdate, [travel start date] }
VAR _lookup =
    LOOKUPVALUE ( TRAINING[on date], TRAINING[employee id], TRAVEL[employee id] )
RETURN
    IF ( _lookup IN _values, "yes", "no" )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

Assuming no repetitions of Emp ID in the training table, create a relationship (Many to One and Single) from the Travel table to the Training table.  Write this calculated column formula in the first table

Match = if(or(related('Training'[on date]='Travel'[travel start Date],abs(related('Training'[on date]-'Travel'[travel start Date])=1),"Yes","No")

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

You can refer to the following calculated column/

Match =
VAR _predate = [travel start date] - 1
VAR _nextdate = [travel start date] + 1
VAR _values = { _predate, _nextdate, [travel start date] }
VAR _lookup =
    LOOKUPVALUE ( TRAINING[on date], TRAINING[employee id], TRAVEL[employee id] )
RETURN
    IF ( _lookup IN _values, "yes", "no" )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.