Retention Rate

[Gaurav_Lakhotia]

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.

[amitchandak]

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

[Gaurav_Lakhotia]

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 ?