topic Re: Intersection - Retention Rate in DAX Commands and Tips

Intersection - Retention Rate

nathov — Wed, 29 Apr 2020 01:08:42 GMT

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)

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?

Re: Intersection - Retention Rate

mahoneypat — Wed, 29 Apr 2020 01:40:16 GMT

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

Re: Intersection - Retention Rate

nathov — Wed, 29 Apr 2020 13:35:50 GMT

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)

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.

Re: Intersection - Retention Rate

Anonymous — Sun, 03 May 2020 11:53:48 GMT

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

Re: Intersection - Retention Rate

nathov — Mon, 04 May 2020 21:19:43 GMT

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