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

[MadalenaBicho]

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! 

[Ashish_Mathur]

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.

[ryan_mayu · Accepted solution]

@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

[anilelmastasi]

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())