Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

latest sales rep

Hi guys

I am looking for solution on the following issue

I have a sales table with cst, sales rep, order cration date and amount
Over the years the sales rep who was responsible for certain customers changed ( the sales rep left or got different customers)
I would like to find out who is the last sales rep that placed an order for cst A, B, ect
I was thinking about these steps

Create a table whith all unique customers, add column which will calculate the latest order date per ech customer and then create another column or measure that will use the latest order date as a filter to decide which sales rep to choose

Please can you help me with this

Thank you very much
Need Help
  • WolfBiber's avatar
    WolfBiber
    8 years ago

    Hey,

    sorry for the late response. Use calculated Table under --> Data-->modeling->New Table

     

    LastReps = DISTINCT(Table1[Customer #])

    Add Columns

     

     

    LastDate = CALCULATE(MAX(Table1[Last order date]);FILTER(Table1;Table1[Customer #]=LastReps[Customer #]))
    SalesRep = LOOKUPVALUE(Table1[Sales Rep];Table1[Customer #];LastReps[Customer #];Table1[Last order date];LastReps[LastDate])

    There are other ways too, but I think this is the most comprohensive

     

     

5 Replies

  • WolfBiber's avatar
    WolfBiber
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hey,

    do you have a fact tables and dimensions?

    Can you show us some example data?

     

    thx

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago
      WolfBiber

      Lets asume that i only have fact table and i am trying to create dimension table where i want to have only one sales rep per cst and that should be the last one placing order

      thank you
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      WolfBiber

       

      and here is a sample 

       

      Customer #Sales RepLast order date
      1018574tim9/21/2017
      1018574alex9/22/2017
      1017640dirk1/12/2018
      1017640pit8/23/2017
      1017640john10/24/2017
      1020601kris1/11/2018
      1020601michael12/15/2017
      1015972Lisa1/11/2018
      1015972carsten1/15/2018
      1015972maria9/4/2017
      1015972tom12/28/2017
      1015981carsten11/13/2017
      1015981niels1/15/2018
      1020722jenny12/1/2017
      1020722peter1/4/2018

       

       

      which i would like to trasfer to new table which will show me this 

       

      1018574alex
      1017640dirk
      1020601kris
      1015972carsten
      1015981niels
      1020722peter

       

       

      meaning dynamic sales rep/cst table based on last order date

       

      Hope this makes it clear 

       

      Thanx 

       

       

       

       



      • WolfBiber's avatar
        WolfBiber
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hey,

        sorry for the late response. Use calculated Table under --> Data-->modeling->New Table

         

        LastReps = DISTINCT(Table1[Customer #])

        Add Columns

         

         

        LastDate = CALCULATE(MAX(Table1[Last order date]);FILTER(Table1;Table1[Customer #]=LastReps[Customer #]))
        SalesRep = LOOKUPVALUE(Table1[Sales Rep];Table1[Customer #];LastReps[Customer #];Table1[Last order date];LastReps[LastDate])

        There are other ways too, but I think this is the most comprohensive