topic Re: Percent of Customers Returned After 90 Days in DAX Commands and Tips

Percent of Customers Returned After 90 Days

beachwaves18 — Fri, 11 Apr 2025 00:31:40 GMT

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.

Re: Percent of Customers Returned After 90 Days

antfr99 — Fri, 11 Apr 2025 08:04:58 GMT

Hi beachwaves18

I suggest use these 4 measures :

TotalCustomers = COUNTROWS(Customer)

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

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)

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

to get the below :

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

Hope this helps

Antonio

Re: Percent of Customers Returned After 90 Days

johnt75 — Fri, 11 Apr 2025 08:53:21 GMT

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

Re: Percent of Customers Returned After 90 Days

Anonymous — Mon, 14 Apr 2025 06:31:11 GMT

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

Re: Percent of Customers Returned After 90 Days

Anonymous — Wed, 16 Apr 2025 07:16:59 GMT

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

Re: Percent of Customers Returned After 90 Days

Anonymous — Wed, 23 Apr 2025 02:56:01 GMT

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

Re: Percent of Customers Returned After 90 Days

Anonymous — Tue, 29 Apr 2025 07:25:52 GMT

Hi beachwaves18,

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

Re: Percent of Customers Returned After 90 Days

xbaby32 — Tue, 16 Sep 2025 16:11:15 GMT

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!

Re: Percent of Customers Returned After 90 Days

johnt75 — Wed, 17 Sep 2025 09:42:26 GMT

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

Re: Percent of Customers Returned After 90 Days

xbaby32 — Wed, 17 Sep 2025 22:44:34 GMT

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?

Re: Percent of Customers Returned After 90 Days

johnt75 — Thu, 18 Sep 2025 08:37:13 GMT

No, that's fine.

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

Re: Percent of Customers Returned After 90 Days

xbaby32 — Fri, 19 Sep 2025 00:46:28 GMT

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

Re: Percent of Customers Returned After 90 Days

johnt75 — Fri, 19 Sep 2025 08:46:54 GMT

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.