cancel
Showing results for
Did you mean:
Member

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

## 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"))`

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

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

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

Member

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

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

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

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

## 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.
Member

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

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

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

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.