Percent of Customers Returned After 90 Days

[beachwaves18]

Hello, I'm trying to create a DAX formula to find the % of customers that returned 90 days after their first purchase. I've spent several hours trying to create and recreate the formula but have not been able to replicate the numbers in the screenshot (for the 3rd or 4th column):

I know there are other posts online about this, but none of the formulas have worked for me or any manipulations. I have a Sales table with CustomerKey and OrderDate. I have a Customer table with CustomerKey and DateFirstPurchase. I've spent many hours working on this and various formulas but this is the one I'm working with now:

Purchase within 90days(%) =

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]) + Returndate ) ) >0 ) )

Return DIVIDE(CountCustomers,[Customers])

Any help or insight is greatly appreciated.

[antfr99 · Accepted solution]

Hi @beachwaves18 

I suggest use these 4 measures :

1.

TotalCustomers = COUNTROWS(Customer)

2.

TotalCustomersByDateFirstPurchase =
CALCULATE(COUNTROWS(Customer), ALLEXCEPT(Customer, Customer[DateFirstPurchase - Customer Table]))

3.

CustomersReturnedWithin90Days =
VAR CustomersWithReturn =
CALCULATETABLE(
VALUES(Sales[Customer Key - Sales Table]),
FILTER(
Sales,
Sales[OrderDate - Sales Table] <= RELATED(Customer[DateFirstPurchase - Customer Table]) + 90
)
)
RETURN
COUNTROWS(CustomersWithReturn)

4.

PercentageReturnedAfter90Days =
DIVIDE([CustomersReturnedWithin90Days], [TotalCustomersByDateFirstPurchase])

to get the below  :

and without the customer name which is hopefully close to your requirement :

Hope this helps

Antonio

[xbaby32]

Hello all,

I need help on this same exercise. I tried the posted solutions but I can't get the Customers Who Returned Within 90 Days measure to work. The measure is either returning a result far too low (3 in July 2001 but it should be ~142) or is returning the same result as Total Customers for each DateFirstPurchase Year/Month. Please help, thank you!

[johnt75]

Make sure that your date table is marked as a date table. If it is then the basic logic behind the solution should work.

[xbaby32]

I double-checked and it is marked as a date table. My date table is calculated by:

Calendar = CALENDAR(min(Sales[OrderDate]),max(Sales[OrderDate]))

Could the fact that the Calendar table is derived from the Sales[OrderDate] matter?

[johnt75]

No, that's fine.

Do you have a customer dimension table which includes their first purchase date ?

[xbaby32]

The correct solution is below. I am looking for the Percent of Customers Returned with 90 Days of First Purchase and Percent Who Returned Within the Following 3 Months.

There is a Sales table that is many:one with the Customers table by CustomerKey. The Customers table has a column DateFirstPurchase. I have a calculated table Calendar one:many with the Sales table by OrderDate.

Sales table
ProductKey	OrderDate	CustomerKey	SalesOrderNumber
376	7/2/03 0:00	16688	SO51189
376	7/7/03 0:00	18212	SO51268
376	7/9/03 0:00	16702	SO51300
376	7/10/03 0:00	18246	SO51321
Customer table
CustomerKey	AltCustomerKey	DateFirstPurchase
21602	11602	1/11/04 0:00
22517	12517	4/21/04 0:00
22518	12518	2/2/04 0:00
22714	12714	1/20/04 0:00
Calendar table
Date
7/2/03 0:00
7/7/03 0:00
7/9/03 0:00
7/10/03 0:00

My intuition is that I need a COUNTROWS measure to count the rows of a table that has all CustomerKeys with a DateFirstPurchase value and the next OrderDate within 90 days of DateFirstPurchase. So if a CustomerKey has a DateFirstPurchase of 8/1/01 and the next OrderDate of 9/1/01, it would be counted. But if a CustomerKey has a DateFirstPurchase of 8/1/01 and the next OrderDate of 12/1/01, it would not be counted as it is outside the 90 day window. For the Customers who returned within the following three months, I assume we can just expand our "window" of next OrderDate from 90 days to 180 days from DateFirstPurchase.

The below measure is what has been proposed previously on this thread, but I tried it and it is not returning the proper result.

Purchase within 90days(%) =
VAR AllCustomers = [Customers]
VAR ReturnWithin90Days =
    SUMX (
        Customerlist,
        VAR FirstPurchase = Customerlist[DateFirstPurchase]
        VAR NumPurchases =
            CALCULATE (
                COUNTROWS ( Sales ),
                DATESINPERIOD ( 'Calendar'[Date], FirstPurchase + 1, 89, DAY )
            )
        VAR Result =
            IF ( NumPurchases > 1, 1 )
        RETURN
            Result
    )
VAR Result =
    DIVIDE ( ReturnWithin90Days, AllCustomers )
RETURN
    Result

My 'Calendar' table is calculated by: 

Calendar = CALENDAR(min(Sales[OrderDate]),max(Sales[OrderDate]))

[johnt75]

Make sure that your calendar table is marked as a date table. Also, it is recommended that a calendar table should always include full years rather than starting and ending on random dates. You could use CALENDARAUTO() or adapt your current code to

Calendar =
CALENDAR (
    DATE ( YEAR ( MIN ( Sales[OrderDate] ) ), 1, 1 ),
    DATE ( YEAR ( MAX ( Sales[OrderDate] ) ), 12, 31 )
)

Separately, you could amend the original code to check if NumPurchases is greater than or equal to 1, currently it is checking for strictly greater than.

[v-pgoloju]

Hi @beachwaves18,

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @beachwaves18,

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @beachwaves18,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @beachwaves18,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Also, thanks to @johnt75  and @antfr99   for the prompt and helpful response.

Just following up to check if the solution shared by our Super User helped resolve your issue. If you're still facing difficulties or need further assistance, please let us know — we’re here to help!
If the response addressed your query, we kindly request you to mark it as Accepted Solution and click Yes if you found it helpful. This supports others in the community as well.

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thank you & Regards,
Prasanna kumar

[johnt75 · Accepted solution]

Try

Purchase within 90days(%) =
VAR AllCustomers = [Customers]
VAR ReturnWithin90Days =
    SUMX (
        Customerlist,
        VAR FirstPurchase = Customerlist[DateFirstPurchase]
        VAR NumPurchases =
            CALCULATE (
                COUNTROWS ( Sales ),
                DATESINPERIOD ( 'Calendar'[Date], FirstPurchase + 1, 89, DAY )
            )
        VAR Result =
            IF ( NumPurchases > 1, 1 )
        RETURN
            Result
    )
VAR Result =
    DIVIDE ( ReturnWithin90Days, AllCustomers )
RETURN
    Result

[antfr99 · Accepted solution]

Hi @beachwaves18 

I suggest use these 4 measures :

1.

TotalCustomers = COUNTROWS(Customer)

2.

TotalCustomersByDateFirstPurchase =
CALCULATE(COUNTROWS(Customer), ALLEXCEPT(Customer, Customer[DateFirstPurchase - Customer Table]))

3.

CustomersReturnedWithin90Days =
VAR CustomersWithReturn =
CALCULATETABLE(
VALUES(Sales[Customer Key - Sales Table]),
FILTER(
Sales,
Sales[OrderDate - Sales Table] <= RELATED(Customer[DateFirstPurchase - Customer Table]) + 90
)
)
RETURN
COUNTROWS(CustomersWithReturn)

4.

PercentageReturnedAfter90Days =
DIVIDE([CustomersReturnedWithin90Days], [TotalCustomersByDateFirstPurchase])

to get the below  :

and without the customer name which is hopefully close to your requirement :

Hope this helps

Antonio