topic Percent Customers Who Returned 90 days after First Purchase in DAX Commands and Tips

Percent Customers Who Returned 90 days after First Purchase

akzimmerman — Sun, 16 Feb 2025 03:11:30 GMT

I am trying to create DAX expressions to determine the percentage of customers that returned 90 days after their first purchase and also determine the percentage of customers that returned 3 months after the first 90 days.

There is a Customers Table which has the following columns: CustomerKey, DateFirstPurchase and there is a Sales Table which has the following columns: CustomerKey, OrderDate.

Below is the end result expected from the full data to be a reference for the ask and the file of the data is uploaded as well. I have been able to determine the first 2 columns of the expected results, but need help on the percentages in the 3rd and 4th columns.

Link to PowerBI

PowerBI Data

I have tried the following DAX measure to get the percent of customers who returned after 90 days, but I am not receiving the correct results. I am unsure what I am missing.

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

Thanks!

Re: Percent Customers Who Returned 90 days after First Purchase

shafiz_p — Sun, 16 Feb 2025 05:07:49 GMT

Hi akzimmerman Try the below measures:

Total Customers = COUNTROWS(Customers)

Customers Returning 90 Days = COUNTROWS( FILTER( Customers, VAR FirstPurchase = Customers[DateFirstPurchase] VAR EndDate90 = FirstPurchase + 90 RETURN CALCULATE( COUNTROWS(Sales), Sales[OrderDate] > FirstPurchase, Sales[OrderDate] <= EndDate90 ) > 0 ) )

% Returning Within 90 Days = DIVIDE( [Customers Returning 90 Days], [Total Customers] )

Customers Returning 90-180 Days = COUNTROWS( FILTER( Customers, VAR FirstPurchase = Customers[DateFirstPurchase] VAR StartDate90 = FirstPurchase + 90 VAR EndDate180 = FirstPurchase + 180 RETURN CALCULATE( COUNTROWS(Sales), Sales[OrderDate] > StartDate90, Sales[OrderDate] <= EndDate180 ) > 0 ) )

% Returning 90-180 Days = DIVIDE( [Customers Returning 90-180 Days], [Total Customers] )

Hope this helps!!

If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

Re: Percent Customers Who Returned 90 days after First Purchase

beachwaves18 — Thu, 10 Apr 2025 23:09:41 GMT

Were you able to figure this out? I'm stuck on it too and have tried several changes to my formula