Evaluate a Sales Rep's New Customer Sales Based On Hire Date

DAX DAX Calculations
1

Hi everyone! I’m trying to evaluate the new business that a Sales Rep is bringing to the company vs. existing business.

I want to do something similar to what you show iPreformatted textn the Calculating New & Existing Customer Sales, but I want to base it on the Hire Date in the Sales Rep table, not the MIN date selected. Would I just swap out MIN(Dates[Date]) with MIN(Sales Rep[Hire Date])? Would that work?
Thanks for your help!

Here’s the DAX for the New Customer Sales that I’m referencing:

New Customer Sales = 
VAR
	Customers = VALUES( Sales[Customer ID] )
RETURN
CALCULATE( [Total Sales],
	FILTER( 
		Customers,
		CALCULATE( COUNTROWS( Sales ), 
			FILTER( ALLSELECTED( Dates ), Dates[Date] < MIN( Dates[Date] ) ) ) = 0 ) )
2

Hi Jewelsdpu

This might work but would depend on your data model, since you haven’t uploaded a file, it’s hard to know

New Customer Sales =

VAR Customers =
VALUES
( Sales[Customer ID] )
VAR SalesRep =
VALUES
( Sales Rep[Rep ID] )
RETURN CALCULATE (
[Total Sales],
FILTER (
Customers,
CALCULATE (
COUNTROWS ( Sales ),
FILTER (
ALLSELECTED ( Dates ),
Dates[Date] < MINX ( SalesRep, Sales Rep[Hire Date] )
)
) = 0
)
)

3

Thank you! I’ll try it and see if it works.

It’s a bit tough for me because I work in the Healthcare industry, so I can’t ever share my files because they contain PHI.

4

@Jewelsdpu,

FYI - Here’s a recent video I put together on how to quickly strip out PHI/PII/BII from your dataset using Power Query so that you can post a version of it on the forum to get more specific solutions.

  • Brian
3 Likes
5

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up

6

Hi @Jewelsdpu, we’ve noticed that no response has been received from you since the 9th of June. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!