cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dsd_CS
Frequent Visitor

Customer Churn and Retention Calculation - Dynamic range

Hi,

 

I am working on a customer retention calculation and need some help. My problem is related to calculating the denominator. To visualize the problem, I created the below dummy data in excel.

 

My definition of the retention rate measure (here: year 4)= All active customers in year 4 / all customers that had their first order at least 4 years ago.


So, according to the visualization, the sum of all yellow marked cells divided by the green marked cells.

 

Spoiler
 dummy data customer churn.PNG

 

In my Power BI file, I created calculated columns for "Customer lifetime (years)" and "time since first order (years)" on my distinct customer table.

The measure for retention denominator should be something like this:

I have the measure for the nominator, but struggle with the denominator. My current formula for the retention denominator measure looks like this:

 

Spoiler
Retention Denominator =
CALCULATE(
    DISTINCTCOUNT(Account[Account Id]),
FILTER(
    ALL(Account),
    Account[Time since first order YEAR] >= Account[Customer lifetime] && Account[Customer lifetime] >= 0
))

 
I get the same total for each column of customer lifetime (in a matrix visual). I am unable to filter based on customer lifetime.

How can I dynamically filter the values for "time since first order" based on the value of "customer lifetime"?

 

I'm fairly new to Power BI but included our two Power BI experts in-house and they didn't know how to solve it. I read through the forum, the examples on daxpatterns.com and in the book "the definitive guide to DAX". I have not implemented the "new vs. returning customers" pattern from daxpattern.com. 

Thank you for your help!

1 ACCEPTED SOLUTION

Hi @dsd_CS,

 

If I understand you correctly, the formula below should work in your scenario. Smiley Happy

Retention Denominator 2 = 
CALCULATE(
    DISTINCTCOUNT('dummy data - customers'[customer ID]),
FILTER(
    ALL('dummy data - customers'),
    'dummy data - customers'[Time since first order YEAR] >= MAX('dummy data - customers'[Customer lifetime])  && 'dummy data - customers'[Customer lifetime] >= 0
))

r2.PNG

 

Regards

View solution in original post

9 REPLIES 9
v-ljerr-msft
Microsoft
Microsoft

Hi @dsd_CS,

 

Could you post your real table structure with some sample/mock data, so that we can better assist on this issue? It's even better that you can just share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. Smiley Happy

 

Regards

Hi @v-ljerr-msft,

 

thank you for your reply. It's a very simple data model. I created some mock data to download here:

 

https://tobii-my.sharepoint.com/:u:/p/dsd/EeI6VQJ4hFhDtT3v8baG5kIBB7hQcU_4d3e2LZfPrjYrvQ?e=WBrAnn


I added a few visualizations to show the different steps. As you can see in visualization "Number of possible customers per lifetime year" my measure doesn't work. It shows the same number in every column. Whereas what I want is that it gives me a cumulative value where "time since first order" is greater or equal "customer lifetime" at lifetime 0.

dummy data customer retention vi.PNG

 

 

Hello,

The link to your mock data is not visible anymore hence I can't follow step by step the solution to your problem (mine too).
Could you please upload the file to a new location?

Best

QS

 

Hi @dsd_CS,

 

If I understand you correctly, the formula below should work in your scenario. Smiley Happy

Retention Denominator 2 = 
CALCULATE(
    DISTINCTCOUNT('dummy data - customers'[customer ID]),
FILTER(
    ALL('dummy data - customers'),
    'dummy data - customers'[Time since first order YEAR] >= MAX('dummy data - customers'[Customer lifetime])  && 'dummy data - customers'[Customer lifetime] >= 0
))

r2.PNG

 

Regards

Anonymous
Not applicable

Hi,

 

This is fantastic.  How woud you calculate a dynamic retention rate in this scenario e.g. 44/327 = 0.13% and then 45/283 =0.16% etc etc

 

Thank you in advance 🙂

Maria

Hi,

 

Please share some data and show the expected result on that data.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

 

Solution.PNG

Hi,

 

Thank you for your prompt reply.

 

Based on the original solution, I was wondering whether it is possible to calculate retention rate for each these from month to month. for example. 

 

Month 1: 44/327 (0,13), 45/283 (0,159), 44/238 (0,184), 33/194 (0,17) etc etc

 

Is this possible to build into this soltuon?

 

Thank you again,

Maria

Hi,

 

This is an old post.  Please share a dataset which i can paste into MS Excel.  On that dataset, please show your expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

wonderful! That's it!

 

Now that I see it, it looks very straightforward. This will make a big difference for us.

 

Thank you!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.