Percent Customers Who Returned 90 days after First Purchase

[akzimmerman]

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!

[beachwaves18]

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

[shafiz_p]

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