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

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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      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.