Intersection - Retention Rate

[nathov]

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 different customers with purchases in the last 3 months (April, March and February) and compare with the customers before that period that would be January. So if 500 customers bought between Apr / Mar / Feb and of those 500, 420 had also bought in January, the retention rate is 420/500 = 84%.

Then I always need to compare the intersection between customers with purchases in the last 3 months with the previous month.

I had created 2 measures:

Retention Rate 1 =
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

COUNTROWS(Intersection)

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

--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
)

RETURN

DIVIDE([Retention Rate 1];COUNTROWS(varCustomersActiveMonthPreviousPeriod90Days))

However, this formula is not working for the beginning of this year (jan/feb/mar). That way, I can't create a chart either, just an indicator according to the month I select. Can anyone help me please?

[Anonymous · Accepted solution]

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

[nathov]

@Anonymous thank you very much for your help!!!!

[mahoneypat]

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]

@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.