topic New vs Existing Customer Based on Sales created date and salesperson in DAX Commands and Tips

New vs Existing Customer Based on Sales created date and salesperson

Anonymous — Tue, 04 May 2021 21:24:35 GMT

Hello

Please help me out here

I'm creating a calculated column with the Customer First Sales Date and in the case where that same customer has a different salesperson it should pick up the 1st sales date ever and not just the 1st sales date for that salesperson. In this case Tom made the 1st sale on 8/4/2008 and that should be Jerry's First Sales Date too. I tried this formula but it doesn't work

VAR
FirstSalesDateKey =
CALCULATE(
MIN('Sales'[sales_date_key])
,ALLSELECTED('Sales'[customer_key])
,ALLEXCEPT('Salesperson','Salesperson'[Salesperson ID])
)

RETURN
IF(
FirstSalesDateKey = BLANK()
,-1
,FirstSalesDateKey
)

Re: New vs Existing Customer Based on Sales created date and salesperson

Greg_Deckler — Tue, 04 May 2021 23:12:35 GMT

Anonymous The problem is your ALLEXCEPT, this is preserving the Sales Person filter. Try replacing ALLEXCEPT with ALL

Re: New vs Existing Customer Based on Sales created date and salesperson

Anonymous — Tue, 04 May 2021 23:55:49 GMT

Greg_Deckler thanks for helping out. I I replaced the ALLEXCEPT with ALL but it didn't work

Re: New vs Existing Customer Based on Sales created date and salesperson

Jihwan_Kim — Wed, 05 May 2021 05:06:16 GMT

Hi, Anonymous

Please check the below picture and the sample pbix file's link down below.

I tried to create a sample pbix file based on your explanation.

All measures are in the sample pbix file.

First Sales Date fix =

IF (

ISFILTERED ( Customers[Customer] ),

IF (

NOT ISBLANK ( [Sales Total] ),

CALCULATE ( MIN ( Sales[Date] ), ALLEXCEPT ( Sales, Customers[Customer] ) )

)

https://www.dropbox.com/s/zjwa6tbwza3kl4w/lefli.pbix?dl=0

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

Re: New vs Existing Customer Based on Sales created date and salesperson

Anonymous — Wed, 05 May 2021 14:46:10 GMT

Thank you @Jihwan_Kim it worked!!