cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to et the sales representatives to the sales list.

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.

 

proscons_4-1660375990793.png

 

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?

 

proscons_5-1660376046906.png

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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.

vstephenmsft_0-1660891073980.png

vstephenmsft_1-1660891092127.png

 

2.Merge like the following.

vstephenmsft_2-1660891134323.png

 

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

vstephenmsft_3-1660891241085.png

vstephenmsft_4-1660891257372.png

 

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.

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

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.

vstephenmsft_0-1660891073980.png

vstephenmsft_1-1660891092127.png

 

2.Merge like the following.

vstephenmsft_2-1660891134323.png

 

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

vstephenmsft_3-1660891241085.png

vstephenmsft_4-1660891257372.png

 

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
Super User
Super User

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
Not applicable

@speedramps 

Thank you for helping!

This was a bit difficult for me 😅

But thank you so much😆

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.