How to Count New Client

[Morrison]

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 🙂 !

[FreemanZ · Accepted solution]

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:

[Morrison]

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

[Morrison]

[Rupak_bi]

plz share sample table.