Need Help Joining Between Two DateTimes & a Foriegn Key

[mccollough]

Hi Everyone!

I have one table that looks like this

Shift _Table
StartDateTime	EndDateTime	Shift	EmployeeID
1/1/2022 8:00	1/1/2022 18:00	Day Shift	1
1/1/2022 16:00	1/2/2022 2:00	Swing Shift	2
1/1/2022 23:00	1/2/2022 9:00	Night Shift	3
1/2/2022 8:00	1/2/2022 18:00	Day Shift	1

And Another table that looks like this

Transactions_Table
TransactionID	TransactionDateTime	EmployeeID
1	1/1/2022 17:30	1
3	1/1/2022 22:00	2
4	1/2/2022 4:30	3
5	1/2/2022 8:30	3

I want to join them so that if a transaction occurs during an employee's shift then they will get credit for that particular transaction on that particular shift. In other words, I'm trying to create something like this by combing the two tables above.

Goal_Table
TransactionID	TransactionDate	EmployeeID	Shift
1	1/1/2022	1	Day Shift
3	1/1/2022	2	Swing Shift
4	1/2/2022	3	Night Shift
5	1/2/2022	3	Night Shift

Any suggestions would be tremendously appreciated!!

Thank you!!

[HoangHugo]

Hi

try this calculate column

Shift = MAXX (CALCULATETABLE(Transaction_table,EARLIER(TransactionDate)>StartDatetime,EARLIER(TransactionDate)<EndDatetime),Shift)

[mccollough]

Hi @HoangHugo ,

Thank you so much for taking the time to respond! I'm trying to implement the solution you described, but PowerBI says it can't find the TransactionDate column even when I use SELECTEDVALUE(). Do you have any other relationships between the columns besides one between EmployeeIDs? 

Thanks again for your help!