Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
waterphil
Regular Visitor

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

 

 

1 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))
2.PNG
 
Regards,
Frank
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

13 REPLIES 13
v-frfei-msft
Community Support
Community Support

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])))

Capture.PNG

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

 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!

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

 

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.

Capture.PNG

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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!

Hi @waterphil,

 

Then update the measure as below.

 

Measure = CALCULATE(DISTINCTCOUNT(vw_busdev_report_revised[Name]),FILTER(vw_busdev_report_revised,[Measure 2]=1))
2.PNG
 
Regards,
Frank
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @waterphil,

 

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


Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi Frank,

 

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

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
Community Champion
Community Champion

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?

Period No RecruitsNew Distributors%# Distributors from previous Period
110039918847 % 
28953130357 % 
39649320942 % 
49036723263 % 
58936916344 % 
68532417755 % 
77551220039 % 
88829211238 % 
99229614449 % 
109330614949 % 
118919911859 % 
12103671928 % 

 

ex. out of 188 new distributors in period 1

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

Anonymous
Not applicable

Hi,

 

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

@waterphil

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.