Forum Discussion

Mario_Sam's avatar
Mario_Sam
New Member
8 months ago
Solved

DAX Mesure

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 🙂

"DAX " "DAX help" 'Need help'
DAX
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    8 months ago

    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.

3 Replies

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Super User
    8 months ago

    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.
  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    8 months ago

    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