Relating Sales Table to Customer Assignments Across Three Fields

[B_Rax]

I'm currently expanding my existing data model to include a bit of an odd field. Currently, our sales data doesn't record the proper outside salesperson based on sales territory. Therefore, we assign salespeople based on a table Customer Assignments which lists customer numbers, their branch, their assigned employees, and the dates those employees were assigned/removed from that customer. This means to relate each sale to its salesperson we have to join on Customer Number, Date, and Branch.

Our sales data is sourced from a database that we only have read access to. The customer assignments table exists on a local server db. We do, however, have a linked connection to that database on the local server from which I attempted to join Sales to Customer Assignments, but it was much too slow.

My next attempt involved creating a compound key (CustomerAssignmentsID) on each table using Branch + Customer Number + Date. To facilitate this I used a query that returns the Customer Assignments table with a row for every date a salesperson was assigned to it since it was originally a date range across two columns. This obviously generates a very large assignment table. This implementation almost works, but its slow and I get strange results. For example, sales over the front counter do not have an outside salesperson assigned. Those rows should be blank for the salesperson column. I still want to see those rows so I have to select "Show items with no data". The rows appear, but their Sales fields like Price and Cost show blank. If I remove the salesperson column they show correctly again. 

Please advise me on this. If there is any more information I can provide let me know!

Here are some examples of my tables:

Customer Assignments modified for compound key:

Desired Results:

Here is my current model after my second attempt:

[v-xuxinyi-msft]

Hi @B_Rax 

Is there some kind of connection between the Date columns in the four tables you gave? Based on the sample data you have now provided, I don't think this can be accomplished.

Best Regards,
Yulia Xu

[B_Rax]

Thanks for the response. Using the modified Customer Assignments table in the second solution leads to relationships that look like this:
Calendar[Date] 1:* Sales[Date]
Sales[CustomerAssingmentsID] *:* CustomerAssigments[CustomerAssigmentsID] (CustomerAssignments Filters Sales)

The invoice date from Sales is part of the compound key CustomerAssignmentsID which relates to the compound key CustomerAssignmentsID in the Customer Assignments table which uses the modified assigned date. The excel example calculated the date as a string which is why it may not look like date is part of the compound key.