topic Re: Calculate daily number of new, repeat and recovered customers in DAX Commands and Tips

Calculate daily number of new, repeat and recovered customers

Anonymous — Thu, 03 Mar 2022 17:48:36 GMT

Hello,

I am working on building a report to show number of new, repeat and recovered customers on any given day. I just started using Power BI about a month ago. I'm learning something new everyday, so some asistance woul be greatly appreciated. There are the requirements:

New: never made a purchase before

Repeat: has made two or more purchases in the last 180 days

Recovered: made purchase more than 180 days ago, and came back to make a purchase in the last 180 days.

I have a date table and customer order history table that looks like the one below. I'm open to any suggestion and willing to change structure of the order history table if it's neccessary.

What I am hoping to get:

Re: Calculate daily number of new, repeat and recovered customers

amitchandak — Fri, 04 Mar 2022 03:25:23 GMT

Anonymous , check these approaches, in case you need daily, change formula to this day vs last day

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

for Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497

Re: Calculate daily number of new, repeat and recovered customers

Anonymous — Mon, 07 Mar 2022 20:14:00 GMT

amitchandak, thanks for the links.

For new customer who never made purchase before regarless of period, how would I do that?

I created the following measure, but getting an error saying the resultset of a query to extenal data source has exceeded the maximum allowed sized of 1M rows.

NewCustomer =

VAR FirstOrderDate = MIN(vCustomerOrders[OrderDate])

RETURN

COUNTROWS(

FILTER(

ALLSELECTED(vCustomerOrders[email]),

isblank(

CALCULATE(COUNTROWS(vCustomerOrders),all(vCustomerOrders[OrderDate]),vCustomerOrders[OrderDate]<FirstOrderDate))))

Re: Calculate daily number of new, repeat and recovered customers

v-cazheng-msft — Wed, 09 Mar 2022 01:41:05 GMT

Hi Anonymous ,

You may try these Measures.

New = VAR PurchasedCx = CALCULATE ( DISTINCTCOUNT ( 'Table'[Name] ), FILTER ( 'Table', 'Table'[Order Date] <= MAX ( 'date table'[Date] ) ) ) VAR AllCx = CALCULATE ( DISTINCTCOUNT ( 'Table'[Name] ), ALL ( 'Table' ) ) RETURN AllCx - purchasedCx

Repeat = VAR PurchsedTable = SUMMARIZE ( VALUES ( 'Table'[Name] ), 'Table'[Name], "OrderNum", CALCULATE ( COUNT ( 'Table'[Order Date] ), FILTER ( 'Table', 'Table'[Order Date] >= ( MAX ( 'date table'[Date] ) - 180 ) && 'Table'[Order Date] <= MAX ( 'date table'[Date] ) ) ) ) VAR CountCx = COUNTX ( FILTER ( PurchsedTable, [OrderNum] >= 2 ), [Name] ) RETURN IF ( ISBLANK ( CountCx ), 0, CountCx )

Recovered = VAR purchasedCx = SUMMARIZE ( VALUES ( 'Table'[Name] ), 'Table'[Name], "OrderNum", CALCULATE ( COUNT ( 'Table'[Order Date] ), FILTER ( 'Table', 'Table'[Order Date] >= ( MAX ( 'date table'[Date] ) - 180 ) && 'Table'[Order Date] <= MAX ( 'date table'[Date] ) ) ), "OrderMoreThan180Days", CALCULATE ( COUNT ( 'Table'[Order Date] ), FILTER ( 'Table', 'Table'[Order Date] < ( MAX ( 'date table'[Date] ) - 180 ) ) ) ) VAR CountCX= COUNTX ( FILTER ( purchasedCx, [OrderNum] >= 2 && [OrderMoreThan180Days] >= 1 ), [Name] ) RETURN IF(ISBLANK(CountCX),0,CountCX)

Then, the result should look like this.

Also, attached the pbix file as reference.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

Re: Calculate daily number of new, repeat and recovered customers

Anonymous — Fri, 11 Mar 2022 01:17:28 GMT

v-cazheng-msft, thanks for your suggestions.

Sorry I didn't explaining clearly on the definition of repeating customers. It's someone who made a purchase this period (for example, today), and also made two more purchases in the last 180 days. I kinda have it figured out, but running into some performance issue. It runs fine if date range is set to last one year, but anything above that will throw an error about consumed memory exceeding 1024MB limit. Do you mind take a quick check on my code to see if it can be optimized? Greatly appreciated! The vCustomerOrders table has about 1.8 million rows.

The [Date Repeating Customer] measure is just:

CALCULATE(min(DateTable[CalendarDate]) )

And here is the Repeating Customers measure: