Customer Retention from previous period

[waterphil]

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 times.

I want to know how many customers remain in the subsequent period

ex. 

Period 1 = 24 customers

Period 2 = 3 of 24 customers

i want to figure out how to get 3 in period 2

[v-frfei-msft]

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]

 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]

here's the file

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

Period	New	Old	Total Customers	Result
1	5	13	18	4
2	1	11	12	0
3	3	6	9	0
Total	9	18	22	4

[v-frfei-msft]

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]

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 · Accepted solution]

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]

Hi @waterphil,

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

Regards,
Frank

[waterphil]

Hi Frank,

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

[waterphil]

Hi Frank,

I think i have to define each column

Recruits - are not yet distributors until they sell and converted to new distributors

New Distributors - i was able to get the new distributors coming from our recruits

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

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

now, im interested to know out of 188 new distributors how many distributors will perform again on Period 2

[themistoklis]

Hello @waterphil,

You are a new member and i can say that this post doesnt help at all to understand your issue.

Could you please provide us with some more information, visuals or even a sample dataset so as to understand your problem better?

[waterphil]

Period No		Recruits	New Distributors	%	# Distributors from previous Period
1	100	399	188	47 %
2	89	531	303	57 %
3	96	493	209	42 %
4	90	367	232	63 %
5	89	369	163	44 %
6	85	324	177	55 %
7	75	512	200	39 %
8	88	292	112	38 %
9	92	296	144	49 %
10	93	306	149	49 %
11	89	199	118	59 %
12	103	67	19	28 %

ex. out of 188 new distributors in period 1

i want to know how many distributors sell again in period 2

[Anonymous]

Hi,

Do you have any ID column for distributors, first of all?

[themistoklis]

@waterphil

Which calculation do you want to make based on the data?