Customer Retention (Continuing from a previous post)

DAX DAX Calculations
1

Hello,

Continuing the discussion from Customer Retention:

Can anyone please help me to get the same result instead of an order placed in one cycle, but two consecutive cycles?
(Customers that enrolled in 2020-C1 placed an order in Cycle 2 and 3)
I tried adding || (Dates[Cycle] = _Cycle +1 || _Cycle +2,), but that didn’t work.

Cycle +1 (CycleID) = 
VAR FirstCycle = MINX( ALLSELECTED( Dates[Cycle] ), [Cycle] )
VAR _Cycle = MAX( Dates[Cycle] )
VAR FirstCust =
    CALCULATETABLE( 
        VALUES( CUSTOMERS[CustomerID] ), 
        TREATAS( VALUES( Dates[Date] ), CUSTOMERS[EnrollmentDate] ),
        Dates[Cycle] = FirstCycle
    )
VAR CustOrders = 
    CALCULATETABLE( 
        VALUES( SALES[CustomerID] ),

Dates[Cycle] = _Cycle +1 || _Cycle +2,

ALL( Dates[CycleText] )
    )
VAR _Result = INTERSECT( FirstCust, CustOrders )
RETURN

COUNTROWS( _Result )

Thank you!

2

Hi @nipunadv,

Give this a go.

Cycle +1 || +2 (CycleID) = 
VAR FirstCycle = MINX( ALLSELECTED( Dates[Cycle] ), [Cycle] )
VAR _Cycle = MAX( Dates[Cycle] )
VAR FirstCust =
    CALCULATETABLE( 
        VALUES( CUSTOMERS[CustomerID] ), 
        TREATAS( VALUES( Dates[Date] ), CUSTOMERS[EnrollmentDate] ),
        Dates[Cycle] = FirstCycle
    )
VAR CustOrders = 
    CALCULATETABLE( 
        VALUES( SALES[CustomerID] ), 
        Dates[Cycle] = _Cycle +1 ||
        Dates[Cycle] = _Cycle +2 ,
        ALL( Dates[CycleText] )
    )
VAR _Result = INTERSECT( FirstCust, CustOrders )
RETURN

COUNTROWS( _Result )

I hope this is helpful

1 Like
3

Thanks @Melissa!!! :slight_smile:
That worked.