Calculating ditinct customers using DAX?

[Anonymous]

Hi

I am having Sales data of more than 200000 records. Below is the dummy data

I want to calculate the following,

1) How many new customers came on a monthly basis?

I want to check how many new customers came on board every month. If Customer A came in Jan, then customer A should not be counted for the rest of the month. I need to calculate the distinct count of customers. I need the output as below

Customer A, B, C, D came in Jan, so they should not be counted as a new customer in other months. I used distinct count, but when I compare it with month, it's not showing the correct result.

2) How many new customers are brought by each salesperson on monthly basis?

I need the same principle as above for Salesperson

Can anyone advise how to do?

[Jihwan_Kim · Accepted solution]

First Month Measure : =

CALCULATE ( MIN ( Data[Month Number] ), REMOVEFILTERS('Calendar'))

New Customers Count : =

VAR _firstmonthbycustomer =

ADDCOLUMNS (

ALLNOBLANKROW ( Customers[Customer] ),

"@firstmonthnumber", [First Month Measure :]

)

VAR _currentmonthnumber =

SELECTEDVALUE ( 'Calendar'[Month Number] )

VAR _currentmonthnew =

FILTER ( _firstmonthbycustomer, [@firstmonthnumber] = _currentmonthnumber )

RETURN

IF ( HASONEVALUE ( 'Calendar'[Month] ), COUNTROWS ( _currentmonthnew ) )

Link to the sample pbix file 

[mahoneypat]

This article shows how most peole usually approach it with the EXCEPT function.  If this is slow, there is a faster pattern you can try by subtracting the distinctcount of the previous month from the distinctcount of the current and previous month combined.

Power BI: New and Repeat Customers - Finance BI (finance-bi.com)

Pat