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
v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi waterphil,

 

To create a measure as below. If it doesn't meet your requirement, kindly share your excepted result to me.

 

Distributors from previous Period = var No = MAX(Table1[Period No])
var per = CALCULATE(SUM(Table1[Recruits]),FILTER(ALL(Table1),Table1[Period No]=No-1))
return
IF(ISBLANK(per),BLANK(),per-CALCULATE(SUM(Table1[New Distributors])))

For more details, please check the pbix as attached.

 

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