DAX Mesure

[Mario_Sam]

I have a customer retention report in Power BI and I’m trying to calculate a measure that represents Active Repeat Customers The business logic is a bit complicated and I could not find a working solution.

This is the requirement:

1-A customer must have made at least 2 purchases within the last 90 days relative to the maximum date selected in the Date slicer. This is considered the rolling window that detects repeat behavior.

2-The same customer must also have made at least 1 purchase in the current Date slicer selection. This check ensures that the customer is currently active in the selected period, not someone who only purchased earlier in the 90-day history.

3-All slicers and filters must continue to affect the calculation correctly. For example, if Region or Customer Segment is filtered, the result should only consider customers in that filtered region or segment.

If the Date slicer selection is October 1 to December 31, then:
-The window for repeat behavior becomes October 2 to December 31 (90 days before max selected date)
-The active condition only checks sales inside the Date slicer selection of October 1 to December 31

A customer should only be counted when both conditions are true:

-They have 2 or more orders within the 90-day rolling window.

-They have at least 1 order within the selected slicer range.

I tried using DISTINCTCOUNT and conditional filters, but I always get wrong results. Either it counts customers who are not active now, or it ignores the slicer completely.

How can I write one correct DAX measure that calculates the number of customers who satisfy both rules?

Thanks in Advance 🙂

[Ahmed-Elfeel · Accepted solution]

Hi @Mario_Sam,

You can create the following DAX measure to correctly handle the rolling window and the current active condition:

Active Repeat Customers (90D) =
VAR MaxSelectedDate =
    MAX ( 'Date'[Date] )

VAR Recent90DaySales =
    CALCULATETABLE (
        Sales,
        DATESINPERIOD (
            'Date'[Date],
            MaxSelectedDate,
            -90,
            DAY
        )
    )

VAR RepeatCustomers =
    FILTER (
        SUMMARIZE (
            Recent90DaySales,
            Sales[CustomerID],
            "OrderCount", COUNTROWS ( Recent90DaySales )
        ),
        [OrderCount] >= 2
    )

VAR ActiveCustomers =
    CALCULATETABLE (
        VALUES ( Sales[CustomerID] ),
        KEEPFILTERS ( 'Date'[Date] )
    )

RETURN
COUNTROWS (
    INTERSECT ( RepeatCustomers, ActiveCustomers )
)

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[Mario_Sam]

Hi @Ahmed-Elfeel

Thanks for the answer it worked ,Thank you so much

[mdaatifraza5556]

Hi @Mario_Sam 

Could you please try the below dax

I am assuming you have the dataset in the below format
1. Dim_date table, sales_fact table and dim_customer table

Active_Repeat_Customers =
VAR Max_Visible_Date =
MAX ( 'Date'[Date] ) -- This will return the max date selected in the slicer

VAR Customers_With2PlusIn_90Days =
FILTER (
VALUES ( 'Customer'[CustomerID] ), 
CALCULATE (
DISTINCTCOUNT ( 'Sales'[OrderID] ),
REMOVEFILTERS ( 'Date' ), 
DATESINPERIOD (
'Date'[Date],
Max_Visible_Date ,
-90,
DAY
)
) >= 2
)

VAR Active_CustomersIn_Selection =
FILTER (
VALUES ( 'Customer'[CustomerID] ),
CALCULATE (
DISTINCTCOUNT ( 'Sales'[OrderID] )
) >= 1
)

VAR Customers_Meeting_Both =
INTERSECT (
Customers_With2PlusIn_90Days ,
Active_CustomersIn_Selection
)
RETURN
COUNTROWS ( Customers_Meeting_Both )


If this answers your questions, kindly accept it as a solution and give kudos