Forum Discussion

mccollough's avatar
mccollough
Helper I
4 years ago

Need Help Joining Between Two DateTimes & a Foriegn Key

Hi Everyone!


I have one table that looks like this

Shift _Table
StartDateTime          EndDateTime          Shift                     EmployeeID
1/1/2022 8:001/1/2022 18:00Day Shift1
1/1/2022 16:001/2/2022 2:00Swing Shift2
1/1/2022 23:001/2/2022 9:00Night Shift3
1/2/2022 8:001/2/2022 18:00Day Shift1


And Another table that looks like this

Transactions_Table
TransactionID                  TransactionDateTime          EmployeeID         
11/1/2022 17:301
31/1/2022 22:002
41/2/2022 4:303
51/2/2022 8:303

 

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          1Day Shift
31/1/20222Swing Shift
41/2/20223Night Shift
51/2/20223Night Shift


Any suggestions would be tremendously appreciated!!

Thank you!!

Need Help

2 Replies

  • HoangHugo's avatar
    HoangHugo
    Solution Specialist
    4 years ago

    Hi

    try this calculate column

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

    • mccollough's avatar
      mccollough
      Helper I
      4 years ago

      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!