Forum Discussion

waterphil's avatar
waterphil
Regular Visitor
7 years ago
Solved

Customer Retention from previous period

I would really appreciate some help with a problem I have very frustratingly failed to solve   I have a file that contains a number of customers per period. Some buy just once, some buy multiple ti...
Show and Tell
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi waterphil,

     

    Then update the measure as below.

     

    Measure = CALCULATE(DISTINCTCOUNT(vw_busdev_report_revised[Name]),FILTER(vw_busdev_report_revised,[Measure 2]=1))
     
    Regards,
    Frank
waterphil's avatar
waterphil
Regular Visitor
7 years ago

 Hi Frank,

 

i think i have to define each column

 

recuits - are not yet distributors 

new distributors - are distributors coming from recruits who do their first sale

 

i want to how many new distributors will perform again on the next period

 

out of 399 recruits in Period 1 we were able to convert 188 new distributors

now, im interested to know out of 188 new distributors in Period 1 how many will stay/sell again on Period 2

 

Thanks!

waterphil's avatar
waterphil
Regular Visitor
7 years ago

here's the file

 

this is the result I need - out of 5 new customers in P1 - 4 customers sell again in P2

PeriodNewOldTotal CustomersResult
1513184
2111120
33690
Total918224

 

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi waterphil,

     

    To create the measures as below.

     

    Measure 2 = 
var no = MAX('Sheet1 (2)'[Periodno])
var t =CALCULATETABLE(VALUES('Sheet1 (2)'[Name]),FILTER(ALL('Sheet1 (2)'),'Sheet1 (2)'[Periodno]<> no ))
return
IF(SELECTEDVALUE('Sheet1 (2)'[New Customer])=FALSE(),BLANK(),IF(SELECTEDVALUE('Sheet1 (2)'[Name])in t,1,0))
    Measure 3 = SUMX('Sheet1 (2)',[Measure 2])

    As the data you shared, the result should be like this.

    Regards,

    Frank

    • waterphil's avatar
      waterphil
      Regular Visitor
      7 years ago

      Hi Frank,

       

      my computer got hung when I tried to use measure 3 in my actual datasets, measure 2 is working fine

    • waterphil's avatar
      waterphil
      Regular Visitor
      7 years ago

      Hi v-frfei-msft

       

      computer got hung if I'm using my actual dataset 
      tried to remove some rows to test, measure 2 is correct but measure 3 gives me an incorrect value

       

      heres my test file

       

      Thanks!

      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi waterphil,

         

        Then update the measure as below.

         

        Measure = CALCULATE(DISTINCTCOUNT(vw_busdev_report_revised[Name]),FILTER(vw_busdev_report_revised,[Measure 2]=1))
         
        Regards,
        Frank