Client Classification

[Fangyi8]

Hi guys,

please I need your help with classifying customers according to the following criteria,

New client - made a one purchase in the month of December 2023,

Active client - made a purchase this month and has made 2 or more purchases in the last 60 days,

Passive client - made a purchase one purchase this month and 1 purchase in the last 60 days,

Returning client - made a purchase this month but hasn't made a purchase in the last 60 days.

Lost client - Hasn't made any purchase in the last 60 days.

I am attaching sample data but if you want to see the whole thing, I can send it.

Deal Stage	Deal id	Date of the deal	Name of clients
Payment received	62	02.04.2022	ООО "Аделит"
Payment received	170	12.09.2022	ООО "Аделит"
Payment received	238	17.12.2022	ООО "Аделит"
Payment received	259	07.01.2023	ООО "Аделит"

[v-zhangti · Accepted solution]

Hi, @Fangyi8 

You can try the following methods.
Sample data:

Classification = 
Var _currentmonth=CALCULATE(COUNT('Table'[Date of the deal]),FILTER('Table',MONTH([Date of the deal])=MONTH(TODAY())
                                                                            &&[Name of clients]=EARLIER('Table'[Name of clients])))
Var _newclient=CALCULATE(COUNT('Table'[Date of the deal]),FILTER('Table',[Name of clients]=EARLIER('Table'[Name of clients])))
Var _last60day=CALCULATE(COUNT('Table'[Date of the deal]),FILTER('Table',[Date of the deal]>=TODAY()-60
                                                                         &&[Date of the deal]<MAX('Table'[Date of the deal])
                                                                        &&[Name of clients]=EARLIER('Table'[Name of clients])))
Return
SWITCH(TRUE(),
_currentmonth=1&&_newclient=1,"New client",
_currentmonth>=1&&_last60day>=2,"Active client",
_currentmonth=1&&_last60day=1,"Passive client",
_currentmonth>=1&&_last60day=BLANK(),"Returning client",
_last60day=BLANK(),"Lost client")

Please check the attachment.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.