Forum Discussion

dsd_CS's avatar
dsd_CS
Frequent Visitor
8 years ago
Solved

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. ...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi dsd_CS,

     

    If I understand you correctly, the formula below should work in your scenario. :smileyhappy:

    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
))

     

    Regards

dsd_CS's avatar
dsd_CS
Frequent Visitor
8 years ago

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.

 

 

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi dsd_CS,

 

If I understand you correctly, the formula below should work in your scenario. :smileyhappy:

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
))

 

Regards

  • dsd_CS's avatar
    dsd_CS
    Frequent Visitor
    8 years ago

    wonderful! That's it!

     

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

     

    Thank you!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

         

        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