Forum Discussion

Johno's avatar
Johno
Frequent Visitor
6 years ago

One Customer two sales reps

Hi, I have sales data going back 13 months. I have customers with more than one sales rep over that time. I need to run historical sales reports using the current sales rep and not the historical sales rep. Has to be based on the current sales rep who has sold the last items to that customer. I have  Fact Sales Detail table and Customers, Date Table, Products and Reps Dim tables.

 

Is there a DAX formula to achieve this?

 

Thanks Rob

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Johno 
    I don't really get you requirement :"I need to run historical sales reports using the current sales rep and not the historical sales rep".  Logically, report is created by data not based other report. In my understanding, do you want make the old reports using the current newest data? If that's the case, you can change the date filter in the dax used in the order report. However, we would need sample data to help you out. 

     

    Read this post to question to get answer quickly:
    https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

     

    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Johno's avatar
      Johno
      Frequent Visitor
      6 years ago

      Hi Paul, the sales detail table goes back 13 months. In that time a customer may have had one sales rep 13 months ago and now currently has a different sales rep. If I run a sales report comparing Mar 20 with March 2019 then I get the below:

       

      Sales Rep,   Total Sales Mar 2019, Total Sales Mar 2020

      Sales Rep A , 121,508  , 101,562 

      Sales Rep B, 0, 31,659

       

      The Mar 2019 sales for Rep B is zero because they were sold by Rep A in Mar 19 and included in Rep A's numbers. But I want those sales for Mar 19 to show for Rep B.  Maybe assigning the sales rep to a customer using the lastest sales date by rep but I cant figure out the DAX to do this.

       

      Thanks Rob