Lost customers in the last year

[awff]

Hi All, 

Below is a small sample of a single client's renewal term:

You can see that "New Customer?" is yes for the first term

But the "Lost Customer?" doesn't seem to be returning the correct value. All rows should be "no" except the last row which should be "yes" as there is no start date for 2020 onwards.

Below is the calculated column measure used in the table.

Lost Customer? = //Customer is lost when no maintenance in the following 12 months
VAR a = 
    RANKX (
        FILTER (
            '_fact (Quote)',
            '_fact (Quote)'[AccountID] = EARLIER ( '_fact (Quote)'[AccountID] ) &&
            year('_fact (Quote)'[Start_Date__c]) <= year('_fact (Quote)'[Start_Date__c]) -1 &&
            '_fact (Quote)'[QuoteStatus (invoiceRequested)] = "Invoiced"
        ),
        '_fact (Quote)'[Start_Date__c],
        ,
        ASC,
        DENSE
    )
RETURN
IF ( a = 1 && '_fact (Quote)'[QuoteStatus (invoiceRequested)] = "Invoiced", "yes", if('_fact (Quote)'[QuoteStatus (invoiceRequested)] <> "Invoiced", blank(), "no"))

 Any assistance is appreciated!

[v-yangliu-msft · Accepted solution]

Hi  @awff ,

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create calculated column.

Lost customer =
var _current='Table'[End date]
var _next=CALCULATE(MAX('Table'[Start Date]),FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])+1))
return
IF(
    _next-_current<=365,"no","yes")

3. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@awff , A new column

new column =

var _max  = minx(filter(Table, Table[Customer] = earlier([Customer]) && [End Date]>  earlier([End date]) ), [Customer])

return

Switch(True(),  [End Date] >= today() , "No " ,

[End Date] < today() && isblank(_max) , "Yes", "No")

[awff]

Hi @amitchandak 

Thank you for your help! I notice the current formula is dynamic in that if the client is a returning customer, the results would change.

Is there a way the formula can be modified to be absolute so that I am able to get a fixed month on month count of attrition?

A lost customer would be defined as:
no renewal since the end date after 1 year (365 days).

In the below example, the second renewal was a two year term, but did not renew for 2021, and would be lost.

after 1 year they return in 2022 and is recognised as a new customer?

Customer	Start Date	End date	New customer?	Lost customer?
Example pty ltd	1/01/2017	1/01/2018	yes	no
Example pty ltd	1/01/2018	1/01/2020	no	yes
Example pty ltd	1/01/2022	1/01/2023	yes	no

Apolgies if i have confused..