How to et the sales representatives to the sales list.

[Anonymous]

Hi I am looking for a solution on how I can get the sales representatives to the sales list.

I have the following list table of sales reps.

The field in the table is the start date of the sales rep's adaptation.

In fact, since quite a lot of changes are made to sales reps.

I thought it would be better to use a formula that could be adapted automatically if the rows of the person in charge include the past, but I could not do it well.

Suppose the following is an example that we want to tie to sales performance.
Is there any way to do this in such a way that if the person in charge of sales is changed in the future, there would be less modeling modification operations?

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

If you have the same start date both in table1 and table2. You could first unpivot the date columns in table1 then merge two tables.

1.Unpivot columns and change the date to date type.

2.Merge like the following.

3.Expand the table, sort the date column and fill down the rep column.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[speedramps]

Do you know abooy of SCDs (slow chanding dimensions)

It looks like you need a SCD Type2

See https://en.wikipedia.org/wiki/Slowly_changing_dimension

and https://www.youtube.com/watch?v=tKeaQpWynzg

SCDs are difficult especialy for Power BI novices. Only attempt it if you are sure you need it.

Also consider when and when not to use SCD for reporting.

It you calcuating sales rep commision restrospectively.

Most organisation favour to report sales but the current sales rep, unless they need to pay commison restrospectively.

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remeber we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

[Anonymous]

@speedramps 

Thank you for helping!

This was a bit difficult for me 😅

But thank you so much😆