Change of sales rep throughout the year

[Anonymous]

Hi,

I'm really hoping someone can help me with this.

I have a sales rep who has retired and all of his clients have switched over to 3 different people. Instead of manually going into my dataset and figuring out if each client has stayed or left, is there a way to do this in power bi or query. I just want to figure out if the new sales rep has maintained that client or if they left the company.

Thanks,

Ivana

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

But seems like a case of SCD. Refer this video from Guyinacube : https://www.youtube.com/watch?v=tKeaQpWynzg

[Anonymous]

Hello,

Thanks for the reply.

Here's an example for 2019:

Account Sales Rep
A Adam
B Barb
C Adam
D Brad
E Sam

However, in 2020 Adam retired and account A went to Barb and account C went to Sam. So it would look like this:

Account Sales Rep
A Barb
B Barb
C Sam
D Brad
E Sam

Some how i need to dax this so when i run a report on total accounts per sales rep I can see that Barb and Sam were able to keep the accounts that were transferred from the retired rep.

Thanks 🙂