One way is to add a colum to the your employee table with a "Date Moved". This is null if it's the employee's current location.
Then we can create a join like:
which will still give us the wrong result, until we add another join (if you can refer to the same column "date" again), or as I couldn't do this using excel, a data source filter:
[Date] <= IFNULL([Date Moved], DATE('1/1/9999'))
Set to "True"
Only available version 10.5.
Use range join for date Sale Date >= Region Start
There are duplication of Sale ID, then remove duplication with picking only newest date of Region Start.
(Already filtered as smaller than Sale date)
Range_Join_SM_10.5.twbx 12.3 KB