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

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

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. :smileyhappy:

 

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
      Microsoft 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!

    • qs's avatar
      qs
      Helper I
      8 years ago

      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