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 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'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:

     

4 Replies

  • 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