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 

[morab]

Hi @lc_finance 

Good Day!

I have the customer transaction data set in the following link. I am trying to create a DAX measure to get the number of repeat customers who have purchased both in base month & any selected month.

For example, Customer code - 100082181 have purchased in October'2019 (Which is the base month). But not purchase in November'2019. So he will not consider as a repeat customer of Nov'19. But have purchased in Dec'19 so he will consider as a repeat customer for Dec'19

Expected Result: In Nov'2019, 28 repeat customers & In Dec'2019, 35 repeat customers considering Oct'2019 as the base month.

Sample data: https://drive.google.com/file/d/1GBrpvtgWkNFPvzNUuZ0a4rB4SXq4prlU/view 

Thanks in advance 🙂

[morab]

Hi,

Good Day!

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

Link: https://www.dropbox.com/s/rb8nto9l6b3xae6/200127_DB14412.xlsx?dl=0

Thanks in advance😊

@lc_finance 

[lc_finance · Accepted solution]

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

[morab]

@lc_finance 

SPOT ON!!!! 😁 

Have already subscribed in your blog @finance-bi.com. Great Content. Looking forward to more to come for beginners (Like Me 😊)

[daedah]

Excuse me please,

I want analysis  repeat customer  from previous month  ,calculate following result by using DAX measures in power BI

Result this below

Month

Jan         Total customer id        repeat customer from previous month

Feb          600                                120

.               630                                 100

.

.

Dec         820                                  200

Thank you so much  for recommend

Raw data is

Date     cust id    custname  amount(Baht)

[lc_finance]

Hi @daedah ,

You can use the following formula:

Repeat customer from previous month = 

SUMX(
    VALUES('Sales'[cust ID]),
    VAR currentCustomerHasSalesPreviousMonth = NOT COUNTX(FILTER('Sales',PREVIOUSMONTH(LASTDATE('Sales'[Date]))),[cust ID]) = BLANK()
    RETURN IF(currentCustomerHasSalesPreviousMonth, 1, 0)
)

This formula works as follows:

- VALUES creates a list of the customers with sales during the current month

- COUNTX counts the sales in the previous month for the customer having sales this month -> these are the repeat customers

- SUMX adds up all repeat customers

You can download from here an example of the Power BI file.

Does this help? Let me know if you have any more questions

LC

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

[daedah]

Repeat customer from previous month =

SUMX(

VALUES('Sale_2017-2019'[CUST_ID]),

VAR currentCustomerHasSalesPreviousMonth = NOT COUNTX(FILTER('Sale_2017-2019',PREVIOUSMONTH('Sale_2017-2019'[BILL_DATE])),[CUST_ID]) = BLANK()

RETURN IF(currentCustomerHasSalesPreviousMonth, 1, 0)

)

But I Can't show results ,where I went wrong ,Recommend again please

[lc_finance]

Hi @daedah ,

Can you share an example of your Power BI file?

You can upload it on OneDrive, Google Drive or a similar tool and then include the link here.

As the formula worked in the sample file, I'll need to see how your file is different from the sample file.

LC

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

[daedah]

Example Raw data

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

Required format

Recommend Please 

Thank you so much

[lc_finance]

Hi @daedah ,

the access to the Google Drive file was blocked so I sent a request for accessing it.

LC

[daedah]

yes please , I  share with you  Already

Thanks

[lc_finance]

Hi @daedah ,

You can download my proposed solution from here.

I updated the formula based on your data model. This is the DAX formula now:

Repeat customer from previous month = 

var datesInPreviousMonth = PREVIOUSMONTH('Sale_2017-2019'[BILL_DATE])
var customersCurrentMonth = VALUES('Sale_2017-2019'[CUST_ID])
var customersPreviousMonth = 
 CALCULATETABLE(
VALUES('Sale_2017-2019'[CUST_ID])
    , ALL('Sale_2017-2019'[BILL_DATE].[Month],'Sale_2017-2019'[BILL_DATE].[MonthNo],'Sale_2017-2019'[BILL_DATE].[Year])
    ,'Sale_2017-2019'[BILL_DATE] IN datesInPreviousMonth)

RETURN SUMX(customersCurrentMonth, IF([CUST_ID] IN customersPreviousMonth,1,0))

Also- I was not sure if you already had formulas for the other columns you wanted: new customer count, new customer sales, and percentage of new customer so I added those formulas for you.

You can find everything in the Power BI solution file.

Finally, this is what the report looks like for 2019:

I hope this helps you! Let me know if you have any question about it.

LC

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

[daedah]

It Great  the Data it Correct

Thank you very much 💙🙏💙

[daedah]

Best Regards,

[daedah]

Thank you very much for your kindly provided this data  to us in my view point , a definition of "a new customer refers to being a new customer starts from the first date of register" not related to time flies  I got the formula already 🙏

I wish to Ark another Question , 

I Would like about the data of new customer since 3 month ago, the new customer who bought the company' product in August 

Did the back to buy again in the 2 month later?

As following the picture

example link for raw data and report format require

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

Thank you very much for help full 🙏

[lc_finance]

Hi @daedah ,

you can download the new solution from here.

As you mention, the new measure will count in October all repeat customers which were new in August or September.

Here is the DAX code:

Repeat customer from new customer 2 months ago = 

var customersCurrentMonth = VALUES('Sale_2017-2019'[CUST_ID])
var dates1MonthAgo = PREVIOUSMONTH('Sale_2017-2019'[BILL_DATE])
var dates2MonthsAgo = PREVIOUSMONTH(dates1MonthAgo)
var datesPast2months = UNION(dates1MonthAgo, dates2MonthsAgo)
var firstDay2MonthsAgo = FIRSTDATE(dates2MonthsAgo)
var customersPast2Months = 
 CALCULATETABLE(
VALUES('Sale_2017-2019'[CUST_ID])
    , ALL('Sale_2017-2019'[BILL_DATE].[Month],'Sale_2017-2019'[BILL_DATE].[MonthNo],'Sale_2017-2019'[BILL_DATE].[Year])
    ,'Sale_2017-2019'[BILL_DATE] IN datesPast2months)
var customersBefore2MonthsAgo = 
 CALCULATETABLE(
VALUES('Sale_2017-2019'[CUST_ID])
    , ALL('Sale_2017-2019'[BILL_DATE].[Month],'Sale_2017-2019'[BILL_DATE].[MonthNo],'Sale_2017-2019'[BILL_DATE].[Year])
    ,'Sale_2017-2019'[BILL_DATE]<firstDay2MonthsAgo)
var newCustomersPast2Months = EXCEPT(customersPast2Months, customersBefore2MonthsAgo )


RETURN SUMX(customersCurrentMonth, IF( [CUST_ID] IN newCustomersPast2Months,1,0))

Here is how it works:

- customersCurrentMonth -> these are the customers in October

- dates1monthAgo -> these are the dates for September

- dates2monthsAgo -> these are the dates for August

- datesPast2months -> these are the dates of August and September

- firstDay2MonthsAgo -> this is the first of August

- customersPast2Months -> these are the customers in August and September

- customersBefore2MonthsAgo -> these are the customer that bought before August

- newCustomersPast2Months -> these are the new customers in August and September

The final SUMX counts all the repeat customers in October, which were new customers in August and September.

Does this help you?

LC

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

[daedah]

Thank you very much for your help 🙏🙏

[daedah]

excuse me please 

I trying calculate dax formula for districtcount number for customer 3 month ago As below formula

districtcount 3 month ago =
var customersCurrentMonth = VALUES('Sale_2017-2019'[CUST_ID])
var dates1MonthAgo = PREVIOUSMONTH('Sale_2017-2019'[BILL_DATE])
var dates2MonthsAgo = PREVIOUSMONTH(dates1MonthAgo)
var dates3Monthsago = PREVIOUSMONTH(dates2MonthsAgo)
var datesPast3months = UNION(dates1MonthAgo, dates2MonthsAgo,dates3Monthsago)
var customersPast3months = CALCULATETABLE(
VALUES('Sale_2017-2019'[CUST_ID])
, ALL('Sale_2017-2019'[BILL_DATE].[Month],'Sale_2017-2019'[BILL_DATE].[MonthNo],'Sale_2017-2019'[BILL_DATE].[Year])
,'Sale_2017-2019'[BILL_DATE] IN datesPast3months)

RETURN SUMX(customersCurrentMonth, IF(
[CUST_ID] IN customersPast3months
,1,0))

but value it mistake  I want to recommend ,How wrong this for dax formula?

Thank you very much

Raw data Link:
https://drive.google.com/file/d/1MhAXgUodUnxXHydyxSAxk91QJgqLbIQ6/view?usp=sharing

[lc_finance]

Hi @daedah ,

could you share the Power BI file where you saw the wrong value and the value you expect to see?

Based on that, I will be happy to help you

LC

[daedah]

Thank you so much,I feel obligated

🙏

Link raw data

https://drive.google.com/file/d/13p15gDDQwo4EAExGqFTzQ7jN3QZqv9OJ/view?usp=sharing

Example septemter .Correct is "1093" but dax formula is 459