topic Re: How to find count of New Customers ? in DAX Commands and Tips

How to find count of New Customers ?

saurabhtd — Tue, 02 May 2023 02:45:08 GMT

Hi everyone,
I will like to ask for help in this case :
I need help in DAX logic for finding count of New Customers.
New Customers = Distinct count of Customer Codes which have grosssalesvalue >0 in current year and grosssalesvalue = 0 in previous year

note : Customer Code, grosssalesvalue are the attributes from sales fact. sales fact and calender dim have relationship.

Thanks in Advance.

Re: How to find count of New Customers ?

Jihwan_Kim — Tue, 02 May 2023 03:21:44 GMT

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Sales: = SUM( Sales[Sales] )

New customer count: = VAR _currentyear = YEAR ( TODAY () ) VAR _currentyearlist = SUMMARIZE ( CALCULATETABLE ( Sales, 'Calendar'[Year] = _currentyear ), Customer[Customer] ) VAR _prevyearlist = SUMMARIZE ( CALCULATETABLE ( Sales, 'Calendar'[Year] = _currentyear - 1 ), Customer[Customer] ) VAR _newcustomerlist = EXCEPT ( _currentyearlist, _prevyearlist ) RETURN COUNTROWS ( _newcustomerlist )

Re: How to find count of New Customers ?

saurabhtd — Tue, 02 May 2023 05:23:25 GMT

Jihwan_Kim Thank for answer. This is working but it is giving answer of distinct count (customercode). How to include sum(sales[grosssalesvalue]) condition in this formula.
New Customers = Distinct count of Customer Codes which have sum(sales[grosssalesvalue]) >0 in current year and sum(sales[grosssalesvalue]) = 0 in previous year