latest sales rep

[Anonymous]

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

[WolfBiber]

Hey,

do you have a fact tables and dimensions?

Can you show us some example data?

thx

[Anonymous]

@WolfBiber

and here is a sample 

Customer #	Sales Rep	Last order date
1018574	tim	9/21/2017
1018574	alex	9/22/2017
1017640	dirk	1/12/2018
1017640	pit	8/23/2017
1017640	john	10/24/2017
1020601	kris	1/11/2018
1020601	michael	12/15/2017
1015972	Lisa	1/11/2018
1015972	carsten	1/15/2018
1015972	maria	9/4/2017
1015972	tom	12/28/2017
1015981	carsten	11/13/2017
1015981	niels	1/15/2018
1020722	jenny	12/1/2017
1020722	peter	1/4/2018

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

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

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

Hope this makes it clear 

Thanx 

[WolfBiber · Accepted solution]

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

[Lalji]

Hello my friend i tried to use above SaleRep Dax but i got this error A table of multiple values was supplied where a single value was expected.

I have created new table for Customer ID and last order date now i need last sales representative for each customer.

My other table is as below

Customer #	Sales Representative Name	Entry Date
3	A	17/04/2019
3	B	29/06/2021
1121	AA	14/02/2016
1121	BB	15/02/2016
1	AAA	5/10/2023
1	BBB	2/10/2023
1199	AAAA	16/10/2023
1199	BBBB	18/10/2023

Result should be

Customer #	Sales Representative Name	Entry Date
3	B	29/06/2021
1121	BB	15/02/2016
1	AAA	5/10/2023
1199	BBBB	18/10/2023

[Anonymous]

@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