Hi,
I am trying to create a measure that returns the number of new customers in selected date that came back for the second time within 3 months a retention metric.
I have three tables : calendar, Customers, Orders.
I don't really know where to start with the measure, that's why i need your help.
I already tried this measure : ( copied from another question) by changing the number of months)
Retention Rate =
var __lastVisibleDate = LASTDATE( 'Calendar'[Date] )
var __shouldCalculate =
// __lastVisibleDate.
NOT(
ISBLANK( NEXTDAY( DATEADD(__lastVisibleDate, -3, month ) ) )
&&
ISBLANK( DATEADD( NEXTDAY( __lastVisibleDate ), -3, month ) )
)
var __result =
if( __shouldCalculate,
var __custWithPurchWithinLast3Months =
CALCULATETABLE(
VALUES( Orders[External ID] ),
DATESINPERIOD(
'Calendar'[Date],
__lastVisibleDate,
-3,
MONTH
)
)
var __lastVisibleDateMinus3Months =
dateadd( __lastVisibleDate, -2, month )
var __custWithPurch1MonthBeforeLast3Months =
CALCULATETABLE(
VALUES( Orders[External ID] ),
DATESINPERIOD(
'Calendar'[Date],
__lastVisibleDateMinus3Months,
-1,
MONTH
)
)
var __custThatBoughtInBothPeriods =
INTERSECT(
__custWithPurchWithinLast3Months,
__custWithPurch1MonthBeforeLast3Months
)
var __ratio =
DIVIDE(
countrows( __custThatBoughtInBothPeriods ),
COUNTROWS( __custWithPurchWithinLast3Months )
)
return
__ratio
)
RETURN
__result
