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

[Chanleakna123]

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. 

[v-yuezhe-msft · Accepted solution]

@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"))

Regards,
Lydia

[Omega]

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.

[Chanleakna123]

Hi , @Omega 

Thanks for your response. 

can you please me the whole packages ? 😞 

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. 

Date	Products	Customers	QTY	Purchasing Customers	Week
1/1/2018	Coke	Panha	10		Week 1
1/2/2018	Sprite	leakna	2
1/3/2018	fanta	sotheary	3
1/4/2018	soda	Sotheara	4
1/5/2018	water	Alian	56
1/6/2018	beer	Julie	7
1/7/2018	fanta	Justin	8
1/8/2018	soda	sotheary	12		Week 2
1/9/2018	water	Sotheara	3
1/10/2018	soda	Alian	4
1/11/2018	water	Julie	54
1/12/2018	beer	Justin	34
1/13/2018	beer	Oscar	67
1/14/2018	beer	Panha	8

[v-yuezhe-msft · Accepted solution]

@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"))

Regards,
Lydia

[Chanleakna123]

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 , 

[v-yuezhe-msft]

@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

[Chanleakna123]

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

[v-yuezhe-msft · Accepted solution]

@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