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: