Customer Status (New, Active, and Lost)

[VanAsten]

We need 3 formulas to return back the correct status for clients based on their purchasing history. The 3 statuses are below:

New = Client's first purchases is within 6 months of max slicer date

Lost = Client has not purchased within 6 months of max slicer date

Active = Client purchased once already and second purchase is less than or equal to 6 months of the max slicer date

We want the statuses to change correctly based on the date range the user is looking at. In the test data above, this client should be "New" in September 2021, and should be "Active" for all the remaining months since they haven't gone without a purchase in a 6 month time frame

Our current formulas are below for each of the statuses:

New = IF(AND([C First Order Date]=[C Last Order Date], ([C First Order Date] >= [Max Slicer Date]-180)), "New", "")

Lost = IF([C Last Order Date] <= [Max Slicer Date]-180, "Lost", "")

Active = IF(AND([New Churn] = "", [Lost Churn] = ""), "Active", "")

[amitchandak]

@VanAsten , You need measure like

rolling 6 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = date(Year(_max), month(_max) -6, Day(_max))+1
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LTD before 6 =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max), month(_max) -6, Day(_max))
var _min = Minx(ALLSELECTED('Date'),'Date'[Date])
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

New = Countx(filter(Values(customer[Customer]), isblank([LTD before 6]) && Not(isblank([Rolling 6])) ), [Customer])

Lost= Countx(filter(Values(customer[Customer]), not(isblank([LTD before 6])) && (isblank([Rolling 6])) ), [Customer])

retain= Countx(filter(Values(customer[Customer]), not(isblank([LTD before 6])) && not(isblank([Rolling 6])) ), [Customer])

refer

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...

[VanAsten]

Hello!

We are getting an error in our "LTD before 6" formula stating "The syntax for ')' is incorrect. Do you know could be causing this?

Also, we are unsure what the '[Net]' field is. What information should be pulled by this field? 

Thank you!

[v-shex-msft]

Hi @VanAsten,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng