Calculate Churn / Retention rate in non-contractual setting

[stherkildsen]

Hey everyone, 

I need some help to create a measure that can calculate the churn rate. 

The way i want the churn rate to be calculated is to distinctcount all the customers who bought in following period:
today () - 731  and today() -366

and see if these customers bought in that period also bought in this period: 
today()-365 and today () - 1

So bascially I want to see how many of the customers who bought between 2 and 1 years ago... also bought the last 365 days. 

E.g 1000 customers bought in this period: today () - 731  and today() -366 -> out of those 1000 customer, 300 bought within the last 365 days which equals a churn rate = 70%

But what is also important is that I want a bar chary with the month on the x axis. So when i look at september is should look the above period back from that month, and the same for august and so on. So that the churn rate changes month to month. 

Im working with an ordertable, where a customer can place several orders . I have the orderdate for all the orders:

I hope the above makes sense and someone can help me, thank you in advance. 

[PaulOlding · Accepted solution]

Hi @stherkildsen 

There was a similar question recently.  I'd suggest the same pattern.  https://community.powerbi.com/t5/DAX-Commands-and-Tips/Customers-without-purchases-in-gt-6-months/m-... 

So, a measure something like:

Churn Percent = 
VAR _CurrentDate = MAX('Date'[Date])
VAR _1To2YearsCustomers =
CALCULATETABLE(
    VALUES(Orders[Customer_ID]),
    DATESBETWEEN('Date'[Date], _CurrentDate - 731, _CurrentDate - 366)
)
VAR _0To1YearsCustomers = 
CALCULATETABLE(
    VALUES(Orders[Customer_ID]),
    DATESBETWEEN('Date'[Date], _CurrentDate - 365, _CurrentDate)
)
VAR _LostCustomers = EXCEPT(_1To2YearsCustomers, _0To1YearsCustomers)
VAR _ChurnPct = 
DIVIDE(
    COUNTROWS(_LostCustomers),
    COUNTROWS(_1To2YearsCustomers)
)
RETURN
    _ChurnPct

I'm not sure I'm getting the calculation right for the churn percent, but you should be able to get there with the above building blocks.  If you needed to see which customers are in 2 virtual tables you can use INTERCEPT rather than EXCEPT.

[stherkildsen]

Ah perfect. I think i works now. What made me confused was the mistake with the - _CurrentDate bc i got 100% chunrate no matter what. But now it seems to work. Thanks :))

[dvl_ctaul]

Do you have a date dimension that connects to that order date?

If I were working on this problem I would try to construct a virtual table that summarizes customer purchases by the specified time periods. So I would have a column for customer, a column for Sum of Purchases in the last 365 days, Sum of purchases between 366 and 731 days from today. I would then write a function that gives me the Distinctcount of customers where sum of purchases in the last 365 days was > 0 and where sum of purchases between 366 and 731 days was > 0 and divide that by the distinct count of customers where sum of purchases in the last 365 days was > 0.

[PaulOlding · Accepted solution]

Hi @stherkildsen 

There was a similar question recently.  I'd suggest the same pattern.  https://community.powerbi.com/t5/DAX-Commands-and-Tips/Customers-without-purchases-in-gt-6-months/m-... 

So, a measure something like:

Churn Percent = 
VAR _CurrentDate = MAX('Date'[Date])
VAR _1To2YearsCustomers =
CALCULATETABLE(
    VALUES(Orders[Customer_ID]),
    DATESBETWEEN('Date'[Date], _CurrentDate - 731, _CurrentDate - 366)
)
VAR _0To1YearsCustomers = 
CALCULATETABLE(
    VALUES(Orders[Customer_ID]),
    DATESBETWEEN('Date'[Date], _CurrentDate - 365, _CurrentDate)
)
VAR _LostCustomers = EXCEPT(_1To2YearsCustomers, _0To1YearsCustomers)
VAR _ChurnPct = 
DIVIDE(
    COUNTROWS(_LostCustomers),
    COUNTROWS(_1To2YearsCustomers)
)
RETURN
    _ChurnPct

I'm not sure I'm getting the calculation right for the churn percent, but you should be able to get there with the above building blocks.  If you needed to see which customers are in 2 virtual tables you can use INTERCEPT rather than EXCEPT.

[stherkildsen]

Hey @PaulOlding , 
I think that the above measure would work. I just have one problem that it says: A table of multiple values was supplied where a single value was expected. 
I think that its maybe because a the same customer places several orders the same day, but im not sure. Can you figure that out? When i use:

Concatenatex(CALCULATETABLE(VALUES('Dynamicweb - Orders'[OrderCustomerEmail]) ,
DATESBETWEEN('Calendar'[Date] , 'Dynamicweb - Orders'[CurrentDate] - 731, 'Dynamicweb - Orders'[CurrentDate] - 366)), 'Dynamicweb - Orders'[OrderCustomerEmail], " , ")
I get all the customers out, but in the wrong format ofc. 

Note: I have a separate calendar table that connects to orderdate

[PaulOlding · Accepted solution]

Hi @stherkildsen 

The VALUES function returns a unique list of values so having customers placing several orders in a day shouldn't be an issue.

On your _0To1Customers line you have a rogue '-' near the end.  Instead of '- _CurrentDate' it should be '_CurrentDate'.  However, that wouldn't be the cause of the error you're getting.  It would just mess up the results.

Your screenshot stops at the RETURN statement.  What are you returning?  If it's one of the variables that is storing a table (eg _LostCustomers) then you'll get the multiple column error.