Repeat Customer

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

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

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)

)

Hi lc_finance 

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