Solved: Re: Repeat Customer - Page 2

morab · ‎10-13-2019

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 · ‎10-13-2019

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

View solution in original post

lc_finance · ‎10-14-2019

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

View solution in original post

lc_finance · ‎10-15-2019

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)

)

View solution in original post

morab · ‎10-16-2019

Hi @lc_finance

View solution in original post

lc_finance · ‎01-27-2020

Hi @morab ,

You can find my solution attached:

https://finance-bi.com/wp-content/uploads/2020/01/customer-transactions-by-month.zip

Here is how it works:

Outlet No = 

var numberOfMonths = SELECTEDVALUE('Number of Months'[number of month])
var outlets = VALUES('Table1'[OutletCode])
var purchaseByMonth = SELECTCOLUMNS(outlets,
"outlets", [OutletCode],
"months with purchase", 
    SUMX(VALUES('Calendar'[Year Month Number]), 
    var countTransactions = CALCULATE(COUNTROWS('Table1'))
    RETURN IF(countTransactions>0,1,0)
    )
)

var purchasedXMonths = COUNTX(FILTER(purchaseByMonth, [months with purchase]=numberOfMonths), [months with purchase])

RETURN purchasedXMonths

The variable number OfMonths is equal to the number of month selected (for example: purchase in only 1 month, purchase in 2 months, etc)

The variable outlets has a list of all the outlets

The variable purchaseByMonth is a table with all the outlets and a column specifying on how many months the outlet bought the product

Finally, the variable purchasedXMonth counts the number of outlets that bought for a specified number of months (based on the variable number of months).

Does this help you?

Regards

LC

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

View solution in original post

lc_finance · ‎11-15-2019

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 · ‎11-17-2019

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

lc_finance · ‎11-19-2019

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 · ‎12-17-2019

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 · ‎11-01-2019

Thank you so much 😍

morab · ‎10-14-2019

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 · ‎10-14-2019

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 · ‎10-14-2019

@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 · ‎10-14-2019

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 · ‎10-14-2019

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 · ‎10-15-2019

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 · ‎10-16-2019

Hi @lc_finance

lc_finance · ‎10-13-2019

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 · ‎10-14-2019

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

Repeat Customer

Helpful resources

Join us at the Microsoft Fabric Community Conference

We want your feedback!

Microsoft Fabric Community Conference 2025

A Year in Review - December 2024

How to Get Your Question Answered Quickly

Join us at the 2025 Microsoft Fabric Community Conference

Repeat Customer

Helpful resources

Join us at the Microsoft Fabric Community Conference

We want your feedback!

Microsoft Fabric Community Conference 2025

A Year in Review - December 2024

How to Get Your Question Answered Quickly