cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Re-purchased customer & Non Repurchased customers for 1 week or N days

Dear everyone, I am new here . I wanna calculate how many customers who  re-purchased the products . 

 

* I have Sale Table which include Daily sale of each customer and QTY and Date . 

 

* my purpose is to calculate : how many customer re-purchased the products after last week ( 7 days ) 

 

If if I use parameter “ 7 days “ the data will show the customers who repeat purchase during the last week and purchase again this week ( Repeat Customers ). 

 

And also I wanna see who lost customer who didn’t purchase the products this week. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Re-purchased customer & Non Repurchased customers for 1 week or N days

@Chanleakna123,

Please perform the following steps in your table.

1.Create the following columns in the table.

Week = WEEKNUM(Table1[Date],2)
days = Table1[Week]*7
Index = RANKX(FILTER(Table1,Table1[Customers]=EARLIER(Table1[Customers])),Table1[Date],,ASC)
pre = CALCULATE(FIRSTNONBLANK(Table1[Customers],1),FILTER(Table1,Table1[Customers]=EARLIER(Table1[Customers])&&Table1[Index]=EARLIER(Table1[Index])-1))
minday = CALCULATE(Min(Table1[days]),ALLEXCEPT(Table1,Table1[Customers]))
maxday = CALCULATE(MAX(Table1[days]),ALLEXCEPT(Table1,Table1[Customers]))


2. Create a new table named para, please note that there is no relationship between para and your original table.

para = DISTINCT(Table1[days])


3. Create the following measures in your original table. For  more details, please review attached PBIX file.

checkcustomer = IF(MAX(Table1[days])=[selectedvalue]&&ISBLANK(MAX(Table1[pre])),"new",IF(MIN(Table1[minday])=[selectedvalue]-7 &&MAX(Table1[maxday])<[selectedvalue],"lost",BLANK()))
count of this week = CALCULATE(COUNTA(Table1[Customers]),FILTER(Table1,Table1[days]=[selectedvalue]))
count of previous week = CALCULATE(COUNTA(Table1[Customers]),FILTER(Table1,Table1[days]=[selectedvalue]-7))
re-purchase = DISTINCTCOUNT(Table1[Customers])-CALCULATE(COUNTA(Table1[Customers]),FILTER(Table1,Table1[checkcustomer] ="lost"|| Table1[checkcustomer]="new"))

Capture.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Moderator v-yuezhe-msft
Moderator

Re: Re-purchased customer & Non Repurchased customers for 1 week or N days

@Chanleakna123,

Create the following measure.

Measure = CALCULATE(COUNT(Table1[Customers]),FILTER(ALLEXCEPT(Table1,Table1[Customers]),Table1[days]<=[selectedvalue]))


Also change re-purchase measure to the following:

re-purchase = CALCULATE(DISTINCTCOUNT(Table1[Customers]),FILTER(Table1,[Measure]>1))



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Omega Established Member
Established Member

Re: Re-purchased customer & Non Repurchased customers for 1 week or N days

Then, you need try counting the products using count function at a customer level using allexcept. If you can share sample data, maybe I can help.

Re: Re-purchased customer & Non Repurchased customers for 1 week or N days

Hi , @Omega 

Thanks for your response. 

can you please me the whole packages ? Smiley Sad 

 

I wanna use parameter , if i filter 7 days , it will show how many customers who purchase the products Last week and also this week.

and who is the New Customer who didn't purchase the products last week but purchcase  this week . 

And i also would like to see who is the lost customer , who purchase the products Last Week but not this week. 

 

Actually , i wish to use Parameter of N days , then i can flexible over this. 

 

 

DateProductsCustomers QTY Purchasing CustomersWeek 
1/1/2018CokePanha 10 Week 1 
1/2/2018Sprite leakna 2 
1/3/2018fantasotheary 3 
1/4/2018soda Sotheara4 
1/5/2018water Alian 56 
1/6/2018beer Julie 7 
1/7/2018fantaJustin 8 
1/8/2018soda sotheary 12 Week 2 
1/9/2018water Sotheara3 
1/10/2018soda Alian 4 
1/11/2018water Julie 54 
1/12/2018beer Justin 34 
1/13/2018beer Oscar67 
1/14/2018beer Panha 8 
Moderator v-yuezhe-msft
Moderator

Re: Re-purchased customer & Non Repurchased customers for 1 week or N days

@Chanleakna123,

Please perform the following steps in your table.

1.Create the following columns in the table.

Week = WEEKNUM(Table1[Date],2)
days = Table1[Week]*7
Index = RANKX(FILTER(Table1,Table1[Customers]=EARLIER(Table1[Customers])),Table1[Date],,ASC)
pre = CALCULATE(FIRSTNONBLANK(Table1[Customers],1),FILTER(Table1,Table1[Customers]=EARLIER(Table1[Customers])&&Table1[Index]=EARLIER(Table1[Index])-1))
minday = CALCULATE(Min(Table1[days]),ALLEXCEPT(Table1,Table1[Customers]))
maxday = CALCULATE(MAX(Table1[days]),ALLEXCEPT(Table1,Table1[Customers]))


2. Create a new table named para, please note that there is no relationship between para and your original table.

para = DISTINCT(Table1[days])


3. Create the following measures in your original table. For  more details, please review attached PBIX file.

checkcustomer = IF(MAX(Table1[days])=[selectedvalue]&&ISBLANK(MAX(Table1[pre])),"new",IF(MIN(Table1[minday])=[selectedvalue]-7 &&MAX(Table1[maxday])<[selectedvalue],"lost",BLANK()))
count of this week = CALCULATE(COUNTA(Table1[Customers]),FILTER(Table1,Table1[days]=[selectedvalue]))
count of previous week = CALCULATE(COUNTA(Table1[Customers]),FILTER(Table1,Table1[days]=[selectedvalue]-7))
re-purchase = DISTINCTCOUNT(Table1[Customers])-CALCULATE(COUNTA(Table1[Customers]),FILTER(Table1,Table1[checkcustomer] ="lost"|| Table1[checkcustomer]="new"))

Capture.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Re-purchased customer & Non Repurchased customers for 1 week or N days

Hi @v-yuezhe-msft really appreciated all your support and very deep dive.Thanks you so much. 

***Repurchased : But can i change few concepts here . 
let say if i am in Week 1 , I'd like to see who Purchase The order more than 1 day , Means if the customers purchase 2 days up During week 1 ( 7 days ) , we call it Re-Purchased , 

If i use N day , for Example : 90 Days , ( the Re-Purchased Customers will show up for those who purchase in any day for 2 days ) , 

If during 90 Days , the Customer purchase only 1 day , we dun call it Re-PUrchased , 

Moderator v-yuezhe-msft
Moderator

Re: Re-purchased customer & Non Repurchased customers for 1 week or N days

@Chanleakna123,

Do you mean that if A customer purchase two products at any two days within 7 days(or 14 days, or 90 days), the customer is a re-purchase customer?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Re-purchased customer & Non Repurchased customers for 1 week or N days

@v-yuezhe-msft 

 

hi dear , 

 

if the customer purchase in any products for 2 days , ( Twice ) during 7 days or 14days or 90 days . we call it Re-Purchased Customer. 
We dun focus much on how many products they order . but instead How many day customers purchase the products During the N Days ? 
If during 7 days , they purchase only 1 day , we dun call it re-purchase , but if 7 days , they purchase 2 days or more than 1 day , we call it Re-purchased customer. 

because our purpose is to see the Re-Purchased Customers Name and QTY of Re-PUrchased customers. 

Moderator v-yuezhe-msft
Moderator

Re: Re-purchased customer & Non Repurchased customers for 1 week or N days

@Chanleakna123,

Create the following measure.

Measure = CALCULATE(COUNT(Table1[Customers]),FILTER(ALLEXCEPT(Table1,Table1[Customers]),Table1[days]<=[selectedvalue]))


Also change re-purchase measure to the following:

re-purchase = CALCULATE(DISTINCTCOUNT(Table1[Customers]),FILTER(Table1,[Measure]>1))



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.