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

      Hi waterphil,

       

      Does that make sense? If so, kindly mark my answer as a solution to close the case.


      Regards,
      Frank