Forum Discussion
Retention Rate
Hi All, I'm trying to calculate retention rate. For this, customer who made purchase within recent 3 months cycle are Active Customer and customer who made purchase before this recent month cycle is Retained Customer.
I'm using Measures,
Active Customer 3Month =
VAR MonthPeriod1 = DATESINPERIOD('Date Table - Orders'[Date],ENDOFMONTH('Date Table - Orders'[Date]),-3,MONTH)
Var Rolling3Month = CALCULATE(SUM('Order Table'[Net Amount]),MonthPeriod1)
Return
IF(Rolling3Month>0,1,BLANK())
Active Customer 6Month =
IF(CALCULATE([Active Customer 3Month],DATEADD('Date Table - Orders'[Date],-3,MONTH))>0,1,BLANK())
Is Retained Customer = IF(AND([Active Customer 3Month]=1,[Active Customer 6Month]=1),1,BLANK())
Retained Count = CALCULATE(COUNT('Order Table'[Order ID]),FILTER('Order Table',[Is Retained Customer]=1))
I'm getting a blank table as result. Please help me out.
2 Replies
Gaurav_Lakhotia , refer
I think you need rolling 3 and 6
example
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-3,MONTH))Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-6,MONTH))Will be blank for the customer that do not have data , so you can use +0 , so get all customers
Make sure date table is marked as date table
new vs repeat customer
https://community.powerbi.com/t5/Desktop/Churn-Rate-lost-Customer/m-p/1173754#M529196
https://blog.enterprisedna.co/new-vs-existing-customers-advanced-analytics-w-dax/
https://www.sqlbi.com/articles/computing-new-customers-in-dax/Hii amitchandak, I need to calculate count of customer who are retained and active.. So with these measures I'm getting blank result. Can you please help me with that ?
