topic Re: How to Count New Client in DAX Commands and Tips

How to Count New Client

Morrison — Fri, 10 Nov 2023 11:12:59 GMT

Hi, from a sales table with

CustomerKey, Date, SalesNetAmount,

I would like to count the number of new customers, all those customers who in 2022 were not in 2021 and all those customers who in 2023 were neither in 2021 nor in 2022. At the same time I would like to see the number of customers lost, all those who no longer bought in 2022 compared to 2021 and who in 2023 no longer bought compared to 2022.

Please help 🙂 !

Re: How to Count New Client

Rupak_bi — Fri, 10 Nov 2023 12:38:38 GMT

plz share sample table.

Re: How to Count New Client

Morrison — Fri, 10 Nov 2023 13:58:36 GMT

Re: How to Count New Client

FreemanZ — Fri, 10 Nov 2023 14:12:10 GMT

hi Morrison ,

not sure if i fully get you, supposing you have a data table like:

CustomerKey	Date	Amt
A	1/1/2020	1
B	1/1/2020	1
C	1/1/2020	1
A	1/1/2021	1
D	1/1/2021	1
A	1/1/2022	1
B	1/1/2022	1
E	1/1/2022	1
F	1/1/2022	1

try to

1) add a calculated column like:

Year = YEAR([date])

2) plot a table visual with the [Year] column and measures like:

NewCount = VAR _priorlist = CALCULATETABLE( VALUES(data[CustomerKey]), data[year]<MAX(data[year]) ) VAR _currentlist =VALUES(data[CustomerKey]) VAR _gaplist = EXCEPT(_currentlist, _priorlist) VAR _result = COUNTROWS(_gaplist) RETURN IF(ISEMPTY(_priorlist), 0, _result)+0

NewList = VAR _priorlist = CALCULATETABLE( VALUES(data[CustomerKey]), data[year]<MAX(data[year]) ) VAR _currentlist =VALUES(data[CustomerKey]) VAR _gaplist = EXCEPT(_currentlist, _priorlist) VAR _result = CONCATENATEX(_gaplist, data[CustomerKey], ", ") RETURN IF(ISEMPTY(_priorlist), "", _result)

LostCount = VAR _prelist = CALCULATETABLE( VALUES(data[CustomerKey]), data[year]=MAX(data[year])-1 ) VAR _currentlist = VALUES(data[CustomerKey]) VAR _gaplist = EXCEPT(_prelist, _currentlist) VAR _result = COUNTROWS(_gaplist) RETURN IF(ISEMPTY(_prelist), 0, _result)+0

LostList = VAR _prelist = CALCULATETABLE( VALUES(data[CustomerKey]), data[year]=MAX(data[year])-1 ) VAR _currentlist =VALUES(data[CustomerKey]) VAR _gaplist = EXCEPT(_prelist, _currentlist) VAR _result = CONCATENATEX(_gaplist, data[CustomerKey], ", ") RETURN IF(ISEMPTY(_prelist), "", _result)

it worked like:

Re: How to Count New Client

Morrison — Mon, 13 Nov 2023 07:42:49 GMT

Hi, thanks for the great solution!! In the source table I also have the name of the agent who sells to that customer and I would like to see the new customers and lost customers per individual agent. Instead of the list, would it be possible to click on the year and view the new customers or lost customers in a table like the one I attach? Thank you 1000