How to deal with salesperson that changed teams

[Anonymous]

Hello Community  -   Currently I have an employee dimension table.    It is mainly comprised of salespeople and some admin staff.   And is mainly used to filter our ERP and our CRM tables.   

Recently, one of our sales team members has shifted from Team A to Team B.    In our ERP system, he will remain on Team A for all of his past orders/shipments.   In his new role, his name will be assigned to any orders so he should not show up on future ERP orders/shipments.  

The issue is that in our Dim Employee table (which has a Team column mapped to the employee), he now needs to change to Team B.  If I use that Team (from the dim table) in a visual that shows shipments and orders, he will be shown as Team B.    This creates a problem for anyone using that table to sum up sales by team.   It will show Team B's sales inflated (since this person is now linked in the dim_table to Team B), and Team A's sales under represented.   

Surely this type of situation must be fairly common.  What is the best way to deal with it?

[Anonymous · Accepted solution]

@CNENFRNL  @ChrisMendoza  Thanks to you both!    Yes, both are interesting and I think the SCD option is probably what best fits our situation.    This article was pretty helpful:  Slowly Changing Dimension (SCD) in Power BI, Part 1, Introduction to SCD - BI Insight

[CNENFRNL]

A typical scenario of "slowly changing dimension"; there are tons of articles on this topic out there.

[ChrisMendoza]

@Anonymous - in our org using an additional field e.g.[JobCode] allows [EmplID] & [JobCode] to become a unique composite key. So maybe you can implement something like that.