Distinct New Customer measure

[Anonymous]

Hi Everyone, 

I have some difficulties find it the right measure for my new customer metrics : 

I have 3 tables in my model : 

1 - Orders 

2 - Customers

3- Calendar ( date table)

I want to create a table that shows all new customers per month and year so i used the following measure : 

New Customers =

COUNTROWS (

FILTER (

ADDCOLUMNS (

VALUES ( Customers[ID] ),

"PreviousSales",

CALCULATE (

COUNTROWS ( Orders),

FILTER (

ALL ( 'Calendar' ),

'Calendar'[Date] < MIN ( 'Calendar'[Date] )

)

)

),

ISBLANK ( [PreviousSales] )

)

)

It works but if i have a new customer that bought twice in the same month, the measure count it twice too. I thought of making another measure where i substract the repeate purchases but it doesnt work. 

Maybe someone know how to delete those duplicate ? 

Thank you in advance !

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Please use Orders[ID] instead of Customers[ID]

New Customers = 
COUNTROWS (
FILTER (
ADDCOLUMNS (
VALUES ( Orders[ID] ),
"PreviousSales",
CALCULATE (
COUNTROWS ( Orders),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] < MIN ( 'Calendar'[Date] )
)
)
),
ISBLANK ( [PreviousSales] )
)
)

Or you could try other methods

https://finance-bi.com/power-bi-new-and-repeat-customers/

https://www.sqlbi.com/articles/computing-new-customers-in-dax/

Best Regards

Maggie

[Anonymous]

Thanks Juanli it's seems to work !! thank you !! 

[amitchandak]

@Anonymous , try like

New Customers =
var _min = MINX (allselected('Calendar'), 'Calendar'[Date] )
return
countx (
FILTER ( summarize (Customers[ID],"PreviousSales",CALCULATE (COUNTROWS ( Orders),FILTER (ALL ( 'Calendar' ),'Calendar'[Date] < _min))
, "_this",COUNTROWS ( Orders)),ISBLANK ( [PreviousSales] ) ) )

unless this month data is not in context it will not give customer

[Anonymous]

@ amitchandak  Thank you for your reply ! 

The measure is not working, it doesnt recognize Customer ID in the formulas....

Cheers

Ilhame