Assign client to Rep after an event

Question

Hello Power BI specialists,&nbsp;I would like a little bit help with the below.&nbsp;I have two tables. A transaction table and sales rep table. The first one includes the client name, the date of transaction and the amount. The second one includes the sales rep, the client and the date of assign.&nbsp;&nbsp;I need to find out which sales rep was assign to the client the date the client repurchase a product. Then I need to create a summary with the sales rep and the count of clients who repurchase at a spesific month.&nbsp;&nbsp;See below example.&nbsp;&nbsp;Clients Purchases&nbsp;&nbsp;NameDateAmountJohn22/04/2019200George06/04/2019200Paul02/04/2019200George31/03/2019200Paul28/03/2019200George25/03/2019200Paul19/02/2019200John05/01/2019200George01/01/2019200&nbsp;Sales Rep&nbsp;&nbsp;NameClientDate of AssignCGeorge25/04/2019BJohn18/04/2019CJohn05/04/2019BPaul01/04/2019APaul15/03/2019BGeorge25/02/2019CPaul20/02/2019AJohn06/01/2019AGeorge02/01/2019&nbsp;Sales RepJan-19Feb-19Mar-19Apr-19A&nbsp;&nbsp;1&nbsp;B&nbsp;&nbsp;11C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;George made his first repurchase on 25/03.He has been assign to sales rep B 25/02. Therefore sales rep B takes 1 on March, for George's repurchase.&nbsp;&nbsp;John made his first repurchase on 22/04.He has been assign to sales rep B on 18/04. Therefore sales rep B takes 1 on April, for John's repurchase.&nbsp;&nbsp;How can I calculate the above with dax?

parry2k · Accepted Answer

Anonymous try following dax to add salesrep in purchase table and then it is easy to creat matrix to get count by sales rep
&nbsp;
SalesRep = 
 CALCULATE( 
     VALUES( Client[Name] ), 
     TOPN( 
         1, 
         FILTER( 
             Client, 
             Client[Client] = Purchase[Name] &amp;&amp;
             Client[Date of Assign] &lt; Purchase[Date]
        ), 
        Client[Date of Assign], 
        DESC 
    )
)

Forum Discussion

Assign client to Rep after an event

2 Replies

Related content

Summing by Client

Priority Based Sales Rep Assignment

Assign (Transfer) Ownership

GITHUB integration with cross client

Buttons assigned to bookmarks not working

Clients Purchases
Name	Date	Amount
John	22/04/2019	200
George	06/04/2019	200
Paul	02/04/2019	200
George	31/03/2019	200
Paul	28/03/2019	200
George	25/03/2019	200
Paul	19/02/2019	200
John	05/01/2019	200
George	01/01/2019	200

Sales Rep
Name	Client	Date of Assign
C	George	25/04/2019
B	John	18/04/2019
C	John	05/04/2019
B	Paul	01/04/2019
A	Paul	15/03/2019
B	George	25/02/2019
C	Paul	20/02/2019
A	John	06/01/2019
A	George	02/01/2019

Sales Rep	Jan-19	Feb-19	Mar-19	Apr-19
A			1
B			1	1
C