Calculate the Number of Customers who have returned within 90 days of FIRST purchase

[jrkhockey33]

I want to get a count of customers who have made a second puchase within 90 days of their first purchase, and then I want to group by the month/year.  I think I am very close here. 

This is what I currently have:

Returning Customers =

VAR

    ExistingCustomers = VALUES( Sales[CustomerKey] )

VAR

    DateParameter = 90

RETURN

COUNTROWS(

    FILTER(

        ExistingCustomers,

        CALCULATE( COUNTROWS( Sales ),

            FILTER( ALLSELECTED('Calendar'[Date]),

                'Calendar'[Date] > ( MIN( 'Calendar'[Date] ) - DateParameter ) &&

'Calendar'[Date] < MIN( 'Calendar'[Date]) ) ) = 0 ) )

The "FILTER(ALLSELECTED..." is something I found on the internet using a date slider, which is not what I want. I don't care about that, I care about the very first purchase date and if that same customer purchased another time within 90 days. 

[beachwaves18]

Are you able to share how you got the Purchased within 90 Days %? I've been stuck on it for awhile

[Anonymous]

Hi @jrkhockey33 ,

You need to find the date of the first purchase first, then use the dax function to delimit the 90-day range.

Is it possible to provide some sample data and expected restuls?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jrkhockey33]

I am unable to find a way to attach a file to this post. 

I do have a column in the customers table that says "First Purchase Date" or we could do MIN(Sales[OrderDate]) to get the first order date. 

[Anonymous]

Hi  @jrkhockey33, 

Is it okay to provide some sample data in the following way? Without sample data, there is really difficult to start.

You could try DATESINPERIOD function like

DATESINPERIOD( 'Calendar'[Date], MIN(Sales[OrderDate])), 90, DAY )

Or just use 'Calendar'[Date] > MIN(Sales[OrderDate]) && 'Calendar'[Date] < MIN(Sales[OrderDate])+90 to ge the 90-day range.

And I doubt MIN(Sales[OrderDate]) to get the first order date seems too accurate.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jrkhockey33]

Customer Table:

CustomerKey	DateFirstPurchase
11602	1/11/2004 0:00
12517	4/21/2004 0:00
12518	2/2/2004 0:00
12714	1/20/2004 0:00

Sales:

OrderDate	CustomerKey	SalesOrderNumber
7/2/2003 0:00	16688	SO51189
7/7/2003 0:00	18212	SO51268
7/9/2003 0:00	16702	SO51300
7/10/2003 0:00	18246	SO51321

The calendar table is simply a table of dates and time attributes (Calendar is a one-to-many relationship to the sales table.)

[Anonymous · Accepted solution]

Hi @jrkhockey33 ,

I did not use the calendar table, the relationship are as follows.

1.Create a measure to calculate the number of 90-day repurchasing for each customer.

Number of 90-day repurchasing =
CALCULATE (
    COUNT ( Sales[CustomerKey] ),
    FILTER (
        ALLSELECTED ( 'Sales' ),
        [CustomerKey] = MAX ( 'Sales'[CustomerKey] )
            && [OrderDate] > MAX ( 'Customer Table'[DateFirstPurchase] )
            && [OrderDate]
                <= MAX ( 'Customer Table'[DateFirstPurchase] ) + 90
    )
)

2.Create another measure to count the number of customers who repurchased within 90 days. 

Number of customers repurchasing =
CALCULATE (
    DISTINCTCOUNT ( 'Sales'[CustomerKey] ),
    FILTER ( 'Sales', [Number of 90-day repurchasing] >= 1 )
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ghimiresuren]

Help Post!
Hello guys,

I found this thread relatable to my issue, where I have been stuck for several days.

As shown in the attached screenshot, I need to calculate monthly percentages for the 90-day and 3-month measures with DAX. Could you please help me?

Best regards,

[adnan85]

Hi, 

Can you give me solution for the measure : Number of customer Who Returned Within Following 3 Months (Following 3 months means- i) If any customer's first purchase was in July, I want to know whether he bought again in Aug, Sept, or Oct. ii) If that customer bought twice in July, that doesn’t count. I only look for whether he came back in Aug-Oct. So July 1 first purchasers and July 31 first purchasers are exactly the same for this one.

[tseayo]

Hi, I used the formula below to solve this question.

Purchasewithin3months =

VAR Customerlist = VALUES( Sales[CustomerKey] )

VAR Returndate = 90

VAR CountCustomers =  

COUNTROWS(

    FILTER(

        Customerlist,

        CALCULATE(

                COUNTROWS (Sales),

                FILTER(

                    ALLSELECTED ( 'Calendar' ),

                    'Calendar'[Date] >=  MIN ( Sales[OrderDate] )  &&

                    'Calendar'[Date] <=  MIN ( Sales[OrderDate]) + 90 &&

                     FORMAT('Calendar'[Date], "YYYYMM") <> FORMAT(MIN(Sales[OrderDate]), "YYYYMM")

            )

    ) > 0

)

)

RETURN

CountCustomers

The slight issue I had with my function is that where the customer purchased in the same, the month will be excluded thus making the count only 60days from date of first purchase.

I hope you find this useful.

Customers who made purchases within first 90days after first purchase and customers who purchased within 90days of first purchase but not in the same month.

[vliste]

Do you have the DAX formula for purchased within 90 days?

[tseayo]

Try this formula and let me know if this works for you:

PurchaseWithin3Months =

VAR CustomerList = VALUES(Sales[CustomerKey])

VAR CountCustomers =

    COUNTROWS(

        FILTER(

            CustomerList,

            CALCULATE(

                COUNTROWS(Sales),

                FILTER(

                    Sales,

                    Sales[CustomerKey] = EARLIER(Sales[CustomerKey]) &&

                    Sales[OrderDate] > CALCULATE(MIN(Sales[OrderDate]), ALLEXCEPT(Sales, Sales[CustomerKey])) &&

                    Sales[OrderDate] <= CALCULATE(MIN(Sales[OrderDate]), ALLEXCEPT(Sales, Sales[CustomerKey])) + 90

                )

            ) > 0

        )

    )

RETURN

CountCustomers