Forum Discussion

nathov's avatar
nathov
Regular Visitor
6 years ago
Solved

Intersection - Retention Rate

I need to create a graph to show customer retention rate. I'll give an example of the rational behind this KPI: If I want the retention rate for April this year, I need to look at all the differe...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Here's the measure:

     

     

    // Assumptions:
// 1. There's a dimension that stores Customers.
// 2. There's a fact table, Sales, that joins on
// 	  CustomerId to Customers and Date to Calendar.
// 3. There's a dimension Calendar which is a proper
//	  calendar in the model (marked as such).

[Retention Rate] =
var __lastVisibleDate = LASTDATE( 'Calendar'[Date] )
var __shouldCalculate =
	// You can calculate the ratio only when
	// you can go back in time 4 months from
	// __lastVisibleDate.
	NOT(
		ISBLANK( NEXTDAY( DATEADD(__lastVisibleDate, -4, month ) ) )
		&&
		ISBLANK( DATEADD( NEXTDAY( __lastVisibleDate ), -4, month ) )
	)
var __result =
	if( __shouldCalculate, 
	
		var __custWithPurchWithinLast3Months =
			CALCULATETABLE(
				VALUES( Sales[CustomerID] ),
				DATESINPERIOD(
					'Calendar'[Date],
					__lastVisibleDate,
					-3,
					MONTH
				)
			)
		var __lastVisibleDateMinus3Months =
			dateadd( __lastVisibleDate, -3, month )
		var __custWithPurch1MonthBeforeLast3Months =
			CALCULATETABLE(
				VALUES( Sales[CustomerID] ),
				DATESINPERIOD(
					'Calendar'[Date],
					__lastVisibleDateMinus3Months,
					-1,
					MONTH
				)
			)
		var __custThatBoughtInBothPeriods =
			INTERSECT(
				__custWithPurchWithinLast3Months,
				__custWithPurch1MonthBeforeLast3Months
			)
		var __ratio =
			DIVIDE(
				countrows( __custThatBoughtInBothPeriods ),
				COUNTROWS( __custWithPurchWithinLast3Months )
			)			
		return
			__ratio
	)
RETURN
	__result

     

     

     

    Please bear in mind that this works for ANY SELECTED PERIOD OF TIME, not only for months.

     

    Best

    D

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

Assuming you have Oct/Nov/Dec data for this measure to work for Jan/Feb/Mar, I am not sure why you need the Retention Rate 2 DAX.  It looks like you have all you need in the Retention Rate 1 part.  Would this work if you used it in your Return?

 

Return

Divide(Countrows(Intersection); Countrows(varActiveCustomers90days))

 

Regards,

Pat

nathov's avatar
nathov
Regular Visitor
6 years ago

mahoneypat thank you, you're right! I can have all in only one formula but it still doesn't working:

 

Retention Rate =
VAR varHighMonthSelected90days = SELECTEDVALUE(Date[FirstdayofMonth])
VAR varLowMonthSelected90days = DATEADD(Date[FirstdayofMonth];-2;MONTH)
VAR varPreviousMonthSelected90days = DATEADD(Date[FirstdayofMonth];-3;MONTH)

--Returns a table with a column with all customers who purchased within 90 days--
VAR varActiveCustomers90days =
CALCULATETABLE(VALUES('Account'[AccountNumber]);
'Date'[FirstdayofMonth]>=varLowMonthSelected90days &&
'Date'[FirstdayofMonth]<=varHighMonthSelected90days
)

--Returns a table with a column with all customers who bought in the period of 1 month prior to 90 days--
VAR varCustomersActiveMonthPreviousPeriod90Days=
CALCULATETABLE(VALUES('Account'[AccountNumber]);
'Date'[FirstdayofMonth]=varPreviousMonthSelected90days
)
--Returns the Intersection between the two tables--
VAR Intersection = INTERSECT(varActiveCustomers90days;varCustomersActiveMonthPreviousPeriod90Days)

RETURN

DIVIDE(COUNTROWS(Intersection);COUNTROWS(varCustomersActiveMonthPreviousPeriod90Days))

 

This formula still doesn't work for the beggining of this year (jan/feb/mar), assuming that I have 2019 data.

 

v-yuta-msft can you please try to help me? You've already helped me with something similar befora. Thank you in advance.