Calculate won and lost customers for SAAS data

[Laila92]

I am trying to calculate (cumulative) won and lost customers for my data. 
My issue is that in my data, each customers can be subscribed to multiple products, that each have their own won and possibly lost date. So a customer can churn on one product, but still be subscribed to another product, in which case i want to count it as a customer. 
See example data: Customer 10021 has multiple IDs, part of them are lost. I want to count it as 1 customer that is won on november 2017.  
Only once all deals are lost for a customer do I want to count it as a lost customer, from the date the last deal was lost.

Example data

I am quite lost on how to work with the different dates for one customer, and how to use this to make a cumulative won customer measure.

[tex628 · Accepted solution]

Hi,

If i understand you correctly you this is the logic:

If all the rows of a customer has a lost date the customer is considered lost. We use the most recent lost date as the official date for that customer. 

If there is a single row that does not have a lost date the customer is still won. Here we use the first existing won date as the official date.

If this is the case i believe the first step is to calculate if each customer is lost or won. I think this calculated column should do the trick:

Column = 
IF(
divide(
Calculate( Countrows('Table') ; all('table') ; [customer] = earlier([customer]))
;
Calculate( Countrows('Table') ; all('table') ; [Lost time] <> BLANK() ;[customer] = earlier([customer]))
;
BLANK()
) = 1 ; "Lost" ; "Won"
)

 
Br,
J

[Laila92 · Accepted solution]

Got it.  Two questions:
1. Why did you do this in a calculated column and not a measure? Would it be possible to do the same as a measure? (Just curious)

2. What is the BLANK() part in the formula for?

[v-lili6-msft]

hi  @Laila92 

You may try to this simple measure as below:

won customers = CALCULATE(DISTINCTCOUNT('Table'[Customer ID]),FILTER('Table','Table'[Lost time]=BLANK()))

Lost customers = CALCULATE(DISTINCTCOUNT('Table'[Customer ID]))- CALCULATE(DISTINCTCOUNT('Table'[Customer ID]),FILTER('Table','Table'[Lost time]=BLANK()))

Regards,

Lin

[Laila92]

the lost measure does include deals that are lost as opposed to customers.

[tex628 · Accepted solution]

Hi,

If i understand you correctly you this is the logic:

If all the rows of a customer has a lost date the customer is considered lost. We use the most recent lost date as the official date for that customer. 

If there is a single row that does not have a lost date the customer is still won. Here we use the first existing won date as the official date.

If this is the case i believe the first step is to calculate if each customer is lost or won. I think this calculated column should do the trick:

Column = 
IF(
divide(
Calculate( Countrows('Table') ; all('table') ; [customer] = earlier([customer]))
;
Calculate( Countrows('Table') ; all('table') ; [Lost time] <> BLANK() ;[customer] = earlier([customer]))
;
BLANK()
) = 1 ; "Lost" ; "Won"
)

 
Br,
J

[Laila92 · Accepted solution]

Got it.  Two questions:
1. Why did you do this in a calculated column and not a measure? Would it be possible to do the same as a measure? (Just curious)

2. What is the BLANK() part in the formula for?

[tex628 · Accepted solution]

1. Measures are generally intended for dynamic calculations, while calculated columns should be used for more static calculations. Whether what you want to calculate is dynamic and prone to change depending on the context you need to use a measure. If you are categorizing something or defining aspects that you know can only be in one way, you should use custom or calculated columns.

2. THE BLANK() is the 3rd argument of the DIVIDE() syntax. What you're basically doing is saying that if you get "Infinity" as a result of the split the result should be BLANK() instead.

[Laila92]

thank you for the answer. how does this look at all the deals?

[tex628]

On every row the column evaluates the total number of deals for the current customer and divides that with the total number of lost deals for the same customer. If that value is 1 that should mean that the customer is lost. 

What result are you getting and what output are you expecting?

Br,
J