Assign client to Rep after an event

[Anonymous]

Hello Power BI specialists,

I would like a little bit help with the below.

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. 

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. 

See below example. 

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

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. 

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. 

How can I calculate the above with dax?

[Ashish_Mathur]

Hi,

Does a 1 in the third table indicate the count of clients that the Sales rep takes credit for in that month?  If yes, then 4 would indicte that there were 4 clients which that sales rep gets credit for in that month?

Is my understanding correct?

[parry2k · Accepted solution]

@Anonymous try following dax to add salesrep in purchase table and then it is easy to creat matrix to get count by sales rep

SalesRep = 
 CALCULATE( 
     VALUES( Client[Name] ), 
     TOPN( 
         1, 
         FILTER( 
             Client, 
             Client[Client] = Purchase[Name] &&
             Client[Date of Assign] < Purchase[Date]
        ), 
        Client[Date of Assign], 
        DESC 
    )
)