Forum Discussion

Morrison's avatar
Morrison
Helper I
2 years ago
Solved

How to Count New Client

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 2...
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    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:

     

FreemanZ's avatar
FreemanZ
Super User
2 years ago

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's avatar
Morrison
Helper I
2 years ago

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