Calculating Lost Customers

Hi, I am trying to calculate Lost and Reactivated customers during the current and past (YEARS).

I am trying to use one of the calculations you provided in the lessons, but, I am having difficulties in getting the results.

``````"Lost Customers = //churn date currently 90 days
VAR CustomersPurchased = CALCULATETABLE( VALUES( Sales[Customer Name Index] ),
FILTER( ALL( Dates ),
Dates[Date] >  MIN( Dates[Date] ) - 365 &&
Dates[Date] <  MIN( Dates[Date] ) - 60 ) )
VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer Name Index] ),
FILTER( ALL( Dates ),
Dates[Date] >  MIN( Dates[Date] ) - 60 &&
Dates[Date] < MIN( Dates[Date] ) ) )
RETURN
COUNTROWS( EXCEPT( CustomersPurchased,  PriorCustomers ) ) * -1``````

Sure happy to help but just placing a formula into the forum is not enough to understand what the issues could be.

Thanks
Sam

Hi Sam,
My apologies for the previous msg,

Here is the snapshot of my dataset:

And the visual:

Intended results for Lost Customers: Need to find the count of customers who has last taken services from us in respective Year.

Intended Results for Reactive Customers: Need to find customers who have resumed taking services after a period of one year.

Kindly request you to assist me in the above Measures.

Ok the idea here is to make sure the ranges here are matching what you need

Based on the evaluation context of your example this doesn’t match up to me.

For Lost customers aren’t you meant to be comparing the customers who purchased last year then didn’t purchase in the current year?

Then you only need to be comparing the customer list in the current context versus the customer list the year prior.

For that, this below part of the formula only need to be…

VAR CustomersPurchased = VALUES( Sales[Customer Name Index] )

Then for this part…

``````PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer Name Index] ),
FILTER( ALL( Dates ),
Dates[Date] >  MIN( Dates[Date] ) - 365 &&
Dates[Date] < MIN( Dates[Date] ) ) )
``````

This should get you want you are describing I believe.

Really think hard about what list of customer you are getting in these variables, this is how you can match up the logic you need with the measure you require to make it happen.

Thanks
Sam