Repeat Customer

[morab]

Hi,

Good Day!

Following is the data table:

OrderDate	Distributor Code	OutletCode	SRName	Category	SKUCode	OrderKG	SalesKG
9/1/2019	14413	100033897	Shipon	DDP	403	0.48	0.48
9/2/2019	14413	100033897	Shipon	DPP	404	1.44	1.44
9/3/2019	14413	100033897	Shipon	DDP	405	0.96	0.96
9/3/2019	14413	100033897	Shipon	DDP	403	0.96	0.96
9/1/2019	14413	100033885	Shipon	DPP	404	1.44	1.44
9/2/2019	14413	100033885	Shipon	DDP	405	0.96	0.96
9/3/2019	14413	100033899	Shipon	DDP	403	0.48	0.48

Based on above table, want to calculate following result by using DAX measures in power BI

By Category, number of repeat customer: 

Category	Repeat Customer #
DDP	1
DPP	0
Total	2

Thanks in advance 🙂

need help 

[lc_finance · Accepted solution]

Hi @morab ,

below is my proposed solution: a formula repeatCustomer that counts the repeat customer based on the chosen category. 

RepeatCustomer = 
VAR customerTable = ADDCOLUMNS(VALUES('Table'[OutletCode]), 
"numberOfPurchases", CALCULATE(COUNTX('Table', [OutletCode])))

RETURN 
SUMX(customerTable, IF([numberOfPurchases]>1,1,0))

And here is a screenshot:

I hope this is what you were looking for.

Regards,

LC

Interested in Power BI finance templates? Check out my blog at www.finance-bi.com

[lc_finance · Accepted solution]

Hi @morab ,

I believe I found the difference: in Excel, you are counting as 'repeat customers' only customers that buy on at least 2 different dates. The formula I proposed counts instead as 'repeat customers' any customer that has more than one transaction, even if the transaction is on the same date.

As an example, outlet code 100032940 for category IFFO: this outlet bought 2 times both on September 26. This customer (2 transactions but on the same day) is not counted in the Excel, but is counted in Power BI.

To have a count based on different days only, I had to add a calculated table to the model. Here is the formula for the table:

Customer Table by Date = 
SUMMARIZE('Datatable','Datatable'[OutletCode],'Datatable'[OrderDate], 'Datatable'[Category])

And here is the new measure, counting repeat customers based on different days:

RepeatCustomerDifferentDays = 
VAR selectedCategory = SELECTEDVALUE('Datatable'[Category])

VAR customerTable = 
CALCULATETABLE (
ADDCOLUMNS(VALUES('Customer Table by Date'[OutletCode]), 
"numberOfPurchases", CALCULATE(COUNTX('Customer Table by Date', [OutletCode])))
, 'Customer Table by Date'[Category]= selectedCategory
)

RETURN 
SUMX(customerTable, IF([numberOfPurchases]>1,1,0))

The result now matches the Excel:

The result now matches the Excel.

Here is the link for the PBI file: https://drive.google.com/open?id=1tjpQKf70VP4gqT-wrjWjL29j-SjlOtwI

Enjoy!

LC

www.finance-bi.com

[lc_finance · Accepted solution]

Here is the new formula, I took the time to simplify it:

RepeatCustomerDifferentDays New = 


SUMX(VALUES('Datatable'[OutletCode]),
    VAR dates =  CALCULATETABLE(VALUES('Datatable'[OrderDate]))
    RETURN IF(COUNTX(dates, [OrderDate])>1,1,0)

)

[morab · Accepted solution]

Hi @lc_finance 

[lc_finance]

HI @daedah ,

I could not load your raw data. It looks like the date is for the year 2062.

Can you share the Power BI file you have with the raw data included?

LC 

[daedah]

https://drive.google.com/file/d/1liL_LOw7xN9CoajiplRUqIvjT7DCYCnI/view?usp=sharing 

[lc_finance]

Hi @daedah ,

I verified and I confirm that the 'number of customers 3 months ago' in September are indeed 459.

I added a table to check this to the Power BI, I called this table customer check. You can download it from here.

With this table, it is easy to see the customers who bought in September, august (month - 1), July (month -2) and June (month -3).

In the end, I checked the customers buying in June plus the past 3 months and I created a sum.

Does this help you?

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

[daedah]

Hi, lc_finance

I tring to calculate DAX measure for power bi ,calculate repeat maintenance by machine_name and Reason each  month

I Use formula

repeat maintenance =

var datesInMonth = VALUES('Date'[Date])

var CurrentMonth = VALUES(Maintenance[plate_id])

var MaintenanceInMonth =

CALCULATETABLE(

VALUES(Maintenance[plate_id])

, All (Maintenance[repair]

,'Date'[Date] IN datesInMonth)

RETURN SUMX(CurrentMonth, IF([plate_id] IN MaintenanceInMonth,1,0))

But someting went wrong

Example data Table Name : Maintenance

Date                          plate_id                              repair
1/11/2019                 A                                         Person

2/11/2019                 B                                         spare part

3/11/2019                 A                                         Person

10/11/2019               C                                         Using

12/11/2019               B                                         spare part

1/12/2019                 A                                         person

Result of November
Machine_Name                     count of maintenance by month

A                                            2

B                                            2

C                                           1

Thank you for you help

[daedah]

Thank you so much 😍

[morab]

Hi,

I have used your suggested formula but not getting following outputs, Can you please help me to get the desire data: 

Data Link: https://www.dropbox.com/sh/8wijg2pogouhg71/AABsUwlOLCYn0KwUWsCNYnIca?dl=0

Output:

@lc_finance need help 

[lc_finance]

Hi @morab ,

I tried applying the formula to your data and it works, see screenshot below.

For example, for Category IFFO there are 17 repeat customers, in the screenshot you have the customer code and how many transactions for each.

I also attach a link with the Power BI file: https://drive.google.com/file/d/1tjpQKf70VP4gqT-wrjWjL29j-SjlOtwI/view?usp=sharing

Regards,

LC

www.finance-bi.com

[morab]

@lc_finance ; 

Thanks a lot for your prompt feedback 🙂

However, repeat customer for IFFO actually 8 rather than 17. I can calculate it in Ms. Excel easily.

Link ( https://www.dropbox.com/s/lj12jexql84iqmq/order_sales.xlsx?dl=0 )

but when I am going to do it in power Bi using your suggested formula its showing different result.

Looking forward to your prompt support 🙂

My Expected result is in below Screenshot: 

[lc_finance · Accepted solution]

Hi @morab ,

I believe I found the difference: in Excel, you are counting as 'repeat customers' only customers that buy on at least 2 different dates. The formula I proposed counts instead as 'repeat customers' any customer that has more than one transaction, even if the transaction is on the same date.

As an example, outlet code 100032940 for category IFFO: this outlet bought 2 times both on September 26. This customer (2 transactions but on the same day) is not counted in the Excel, but is counted in Power BI.

To have a count based on different days only, I had to add a calculated table to the model. Here is the formula for the table:

Customer Table by Date = 
SUMMARIZE('Datatable','Datatable'[OutletCode],'Datatable'[OrderDate], 'Datatable'[Category])

And here is the new measure, counting repeat customers based on different days:

RepeatCustomerDifferentDays = 
VAR selectedCategory = SELECTEDVALUE('Datatable'[Category])

VAR customerTable = 
CALCULATETABLE (
ADDCOLUMNS(VALUES('Customer Table by Date'[OutletCode]), 
"numberOfPurchases", CALCULATE(COUNTX('Customer Table by Date', [OutletCode])))
, 'Customer Table by Date'[Category]= selectedCategory
)

RETURN 
SUMX(customerTable, IF([numberOfPurchases]>1,1,0))

The result now matches the Excel:

The result now matches the Excel.

Here is the link for the PBI file: https://drive.google.com/open?id=1tjpQKf70VP4gqT-wrjWjL29j-SjlOtwI

Enjoy!

LC

www.finance-bi.com

[morab]

Hi @lc_finance 

Thanks a lot for your kind cooperation.

You are Superb!!! 

Its working for me.

I would like to request, can you please help me to have slicer option (of followings columns) for "Repeat Customer Different Days" also. Currently which is working only for "Repeat Customer". 

- by "Category" (already have the solution in your shared measures)

- by "SRName"

- by "SKUCode"

- by "Route"

Here is the link for the Excel file: https://www.dropbox.com/s/lj12jexql84iqmq/order_sales.xlsx?dl=0

Here is the Screenshot (MS.EXCEL) :

Looking forward to your kind cooperation 🙂

need help 

[lc_finance · Accepted solution]

Here is the new formula, I took the time to simplify it:

RepeatCustomerDifferentDays New = 


SUMX(VALUES('Datatable'[OutletCode]),
    VAR dates =  CALCULATETABLE(VALUES('Datatable'[OrderDate]))
    RETURN IF(COUNTX(dates, [OrderDate])>1,1,0)

)

[morab · Accepted solution]

Hi @lc_finance 

[lc_finance · Accepted solution]

Hi @morab ,

below is my proposed solution: a formula repeatCustomer that counts the repeat customer based on the chosen category. 

RepeatCustomer = 
VAR customerTable = ADDCOLUMNS(VALUES('Table'[OutletCode]), 
"numberOfPurchases", CALCULATE(COUNTX('Table', [OutletCode])))

RETURN 
SUMX(customerTable, IF([numberOfPurchases]>1,1,0))

And here is a screenshot:

I hope this is what you were looking for.

Regards,

LC

Interested in Power BI finance templates? Check out my blog at www.finance-bi.com

[morab]

Thanks a lot for the solution 

---

@lc_finance wrote:

Hi @morab ,

below is my proposed solution: a formula repeatCustomer that counts the repeat customer based on the chosen category. 

RepeatCustomer = 
VAR customerTable = ADDCOLUMNS(VALUES('Table'[OutletCode]), 
"numberOfPurchases", CALCULATE(COUNTX('Table', [OutletCode])))

RETURN 
SUMX(customerTable, IF([numberOfPurchases]>1,1,0))

And here is a screenshot:

I hope this is what you were looking for.

Regards,

LC

Interested in Power BI finance templates? Check out my blog at www.finance-bi.com

---