Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
@Anonymous thank you very much for your help!!!!
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |