Calculate daily number of new, repeat and recovered customers

[sonicfish]

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:

[v-cazheng-msft]

Hi @sonicfish ,

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

[sonicfish]

@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: 

[sonicfish]

@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))))

[amitchandak]

@sonicfish , 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/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

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

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...