Forum Discussion
Calculated column to return True or False depending on whether the individual is a new client
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
Super UserHello 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())