Identification of returning/reactivating customers after loss

[awff]

edit: Fixed errors in the sample table and dax
Hello, 🖐

I'm looking to identify customers who return after being considered lost.

Basically, you need to find the earliest start date after the last "lost" instance.

Below is a sample table with a column showing the desired result:

You might assume that rows that are not new, lost, or returned are continuous renewals.

SalesData = 
DATATABLE (
    "Customer", STRING,
    "Start_Date", DATETIME,
    "End_date", DATETIME,
    "IS_NEW", STRING, --Earliest instance of the start date
    "IS_LOST", STRING, --Based on the end date, TRUE if the next start date is more than 30 days since the last end date or if no future renewals exist.
    "Desired Result", STRING, -- Returned if the earliest start date since previous lost.
    {
         { "ABC Company", "1/06/2017", "1/07/2017", "New", blank(),blank()  },
         { "ABC Company", "1/06/2017", "1/07/2017", "New", blank(), blank()},
         { "ABC Company", "1/07/2017", "1/08/2017", blank(), blank(), blank()},
         { "ABC Company", "1/08/2017", "1/09/2017", blank(), blank(), blank() },
         { "ABC Company", "1/09/2017", "1/10/2017", blank(), blank(), blank() },
         { "ABC Company", "1/10/2017", "1/12/2017", blank(), "Lost", blank() }, --Lost, as next start date is more than 30 days from last end date.
         { "ABC Company", "1/11/2017", "1/12/2017", blank(), "Lost", blank() }, --Lost, as next start date is more than 30 days from last end date.
         { "ABC Company", "1/01/2018", "1/02/2018", blank(), blank(), "Returned" }, --Earliest start date since last lost.
         { "ABC Company", "1/01/2018", "1/03/2018", blank(), blank(), "Returned" }, --Earliest start date since last lost.
         { "ABC Company", "1/02/2018", "1/03/2018", blank(), blank(), blank() },
         { "ABC Company", "1/03/2018", "1/04/2018", blank(), blank(), blank() },
         { "ABC Company", "25/03/2018", "1/05/2018", blank(), "Lost", blank() }, ---Lost, as next start date is more than 30 days from last end date.
         { "ABC Company", "1/07/2018", "1/08/2018", blank(), "Lost", "Returned" }, --Lost, as next start date is more than 30 days from last end date. Returned, as earliest start date since previous lost.
         { "ABC Company", "1/10/2018", "1/11/2018", blank(), blank(), "Returned" }, -- Returned, as earliest start date since previous lost.
         { "ABC Company", "1/11/2018", "1/01/2019", blank(), "Lost", blank() }, -- Lost as no futher renewals exist.
         { "ABC Company", "1/11/2018", "1/01/2019", blank(), "Lost", blank() } -- Lost as no futher renewals exist.
    }
)

Below is a calculated column measurement that appears to be the closest, but seems to apply it to all dates after the earliest start date since it was lost. How can I make the _LastEndDate variable not apply to all rows after the desired instance is returned?

Is_Returning_Customer = //LOGIC: Customer is reactivated if previously lost (latest end date lapsed or new start date is older than end date by 30 days)
var _prevSales = SalesData[Customer]
var _prevEnd = SalesData[End_date]
var _prevStart = SalesData[Start_Date]
var _prevLost = SalesData[IS_LOST]
VAR _LastEndDate = --look for the very latest end date for the account
CALCULATE(
    MAX(SalesData[End_date]),
    FILTER(
        ALL(SalesData),
        SalesData[Customer] = _prevSales &&
        SalesData[IS_LOST] = "Lost" &&
        SalesData[Start_Date] < _prevEnd))
return
Switch(
    true(),
    ISBLANK(_LastEndDate), blank(),
    SalesData[Start_Date] > _LastEndDate + 30, "Returned", 
    blank()
)

Thank you!

[awff]

Hi Pat,

I have seen the linked page along with other articles but neither return the correct result as they seem to be more suited for point sales with a single date vs SAAS model.

To further complicate, there will be instances where subscription dates will overlap if the client wishes to increase the number of seats. But I don't think this should be a factor for returning customers.

Just need it to either find the first start date immediate from each last lost date and no further or the same, but the last end date when lost.

[mahoneypat]

Have you seen this article?

New and returning customers – DAX Patterns

Pat