Forum Discussion

MadalenaBicho's avatar
MadalenaBicho
Regular Visitor
1 year ago
Solved

Calculated column to return True or False depending on whether the individual is a new client

Good afternoon,

 

I have a table that looks like the below: 

 

Name           Approach date

Client 1          01/01/2025          

Client 2          04/01/2025              

Client 3          01/02/2025                

Client 2          03/02/2025           

Client 1          05/02/2025             

Client 3          01/03/2025             

Client 4          12/03/2025                

Client 5          14/03/2025                   

 

My manager needs to know the number of clients that are new every month. How would I go about getting a calculated column that shows 'New client' for new clients and 'Not new client' for clients that aren't, or 'True' or 'False, something along those lines? I have tried a few things but it seems to get confused when a new client approaches us more than once within the same month. Please see below what I would like to see: 

 

Name                Date                       New/not new client

Client 1          01/01/2025                   New client

Client 2          04/01/2025                   New client

Client 3          01/02/2025                   New client

Client 2          03/02/2025                   Not new client

Client 1          05/02/2025                   Not new client

Client 3          01/03/2025                   Not new client

Client 4          12/03/2025                   New client

Client 5          14/03/2025                   New client

 

Furthermore, this might be more complicated, but I would also like to have two other calculated columns:

- One that shows 'long-term clients', meaning those that have approached us in at least three separate months within the past year (12 months). 

- One that shows 'returning' clients, meaning those that have approached us again after no contact for 180 days. 

 

Any help would be greatly appreciated as I've been racking my brain over this one. 

 

Many thanks in advance! 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    MadalenaBicho 

    you can try this to create calculated columns

     

    yearmonth = year('Table'[Date])*100+month('Table'[Date])
     
    New/not new client =
    var _last=maxx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])
    return if (ISBLANK(_last),"New Client","Not new client")
     
    long-term client =
    VAR _date=EDATE('Table'[Date],-12)
    var _count=CALCULATE(DISTINCTCOUNT('Table'[yearmonth]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]<=EARLIER('Table'[Date])&&'Table'[Date]>_date))
    return if (_count>=3,"Y")
     
    returning clients =
    var _last=maxx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])
    return if (DATEDIFF(_last,'Table'[Date],DAY)>180,"Y")
     
     
    pls see the attachment below
     
     

     

     

3 Replies

  • anilelmastasi's avatar
    anilelmastasi
    Super User
    1 year ago

    Hello MadalenaBicho 

     

    Can you try these formulas:

     

    IsNewClientInMonth =
    VAR ClientName = [Name]
    VAR CurrentDate = [Approach date]
    VAR CurrentMonth = EOMONTH(CurrentDate, 0)

    VAR FirstContactInMonth =
    CALCULATE(
    MIN('YourTable'[Approach date]),
    FILTER(
    'YourTable',
    [Name] = ClientName &&
    EOMONTH([Approach date], 0) = CurrentMonth
    )
    )

    RETURN
    IF(CurrentDate = FirstContactInMonth, "New client", "Not new client")


    IsLongTermClient =
    VAR ClientName = [Name]
    VAR CurrentDate = [Approach date]
    VAR StartDate = EDATE(CurrentDate, -12)

    VAR MonthsContacted =
    CALCULATETABLE(
    DISTINCT(
    SELECTCOLUMNS(
    FILTER(
    'YourTable',
    [Name] = ClientName &&
    [Approach date] >= StartDate &&
    [Approach date] <= CurrentDate
    ),
    "Month", FORMAT([Approach date], "YYYY-MM")
    )
    )
    )

    VAR MonthCount = COUNTROWS(MonthsContacted)

    RETURN IF(MonthCount >= 3, "Long-term client", BLANK())

     

     

    IsReturningClient =
    VAR ClientName = [Name]
    VAR CurrentDate = [Approach date]

    VAR PreviousDate =
    CALCULATE(
    MAX('YourTable'[Approach date]),
    FILTER(
    'YourTable',
    [Name] = ClientName &&
    [Approach date] < CurrentDate
    )
    )

    VAR Gap = DATEDIFF(PreviousDate, CurrentDate, DAY)

    RETURN
    IF(NOT(ISBLANK(PreviousDate)) && Gap >= 180, "Returning client", BLANK())

     



  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    MadalenaBicho 

    you can try this to create calculated columns

     

    yearmonth = year('Table'[Date])*100+month('Table'[Date])
     
    New/not new client =
    var _last=maxx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])
    return if (ISBLANK(_last),"New Client","Not new client")
     
    long-term client =
    VAR _date=EDATE('Table'[Date],-12)
    var _count=CALCULATE(DISTINCTCOUNT('Table'[yearmonth]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]<=EARLIER('Table'[Date])&&'Table'[Date]>_date))
    return if (_count>=3,"Y")
     
    returning clients =
    var _last=maxx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])
    return if (DATEDIFF(_last,'Table'[Date],DAY)>180,"Y")
     
     
    pls see the attachment below
     
     

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Write these calculated column formulas

    Month number = MONTH(Data[Date])

    Status = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Month number]<EARLIER(Data[Month number])))<>BLANK(),"Not new client","New client")

    Hope this helps.