New vs Existing Customer Based on Sales created date and salesperson

[Anonymous]

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
) 

[Jihwan_Kim · Accepted solution]

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

[Anonymous]

Thank you @Jihwan_Kim it worked!!

[Greg_Deckler]

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

[Anonymous]

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