cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
miba Member
Member

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
1 ACCEPTED SOLUTION

Accepted Solutions
WolfBiber Member
Member

Re: latest sales rep

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

 

 

View solution in original post

4 REPLIES 4
WolfBiber Member
Member

Re: latest sales rep

Hey,

do you have a fact tables and dimensions?

Can you show us some example data?

 

thx

miba Member
Member

Re: latest sales rep

@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
miba Member
Member

Re: latest sales rep

@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 Member
Member

Re: latest sales rep

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

 

 

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 372 members 3,915 guests
Please welcome our newest community members: