Forum Discussion

Mario_Sam's avatar
Mario_Sam
New Member
9 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 sol...
"DAX " "DAX help" 'Need help'
DAX
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    9 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
Icon for Super User rankSuper User
9 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