Customer Retention Part 2: Period over Period Retention 09-17-2020 03:50 AM

[amitchandak]

Objective: Period over Period Retention is a comparison of one period vs another period. These periods are heterogeneous. The definition of the new retained and lost customers is only based on 2 periods of data i.e. like the customer did not visit in the last 3 months but his last visit was 12 months before. Basically, he did not come in 9 months before the above 3-month period.

Rolling 12 = CALCULATE([Sales],DATESINPERIOD('Date'[Date],ENDOFMONTH('Date'[Date]),-12,MONTH))  
Rolling 3 = CALCULATE([Sales],DATESINPERIOD('Date'[Date],ENDOFMONTH('Date'[Date]),-3,MONTH))  
Rolling 9 before three = CALCULATE([Sales],DATESINPERIOD('Date'[Date],eomonth(max('Date'[Date]),-3),-9,MONTH)) 

Lost Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([Rolling 3]) && not(ISBLANK([Rolling 9 before three])) , 1,BLANK()))
New Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([Rolling 9 before three]) && not(ISBLANK([Rolling 3])) , 1,BLANK()))
Retained Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(not(ISBLANK([Rolling 3])) && not(ISBLANK([Rolling 9 before three])) , 1,BLANK()))

• This Period: Rolling 3-month Sales
• Last Period: Rolling 9 Before 3 months Sales.
• New:  IF This Period is NOT Blank and Last Period is Blank
• Lost:  IF Last Period is NOT Blank and This Period is Blank
• Retain: IF Last Period is NOT Blank  and This Period is NOT Blank

eyJrIjoiNjljM2E3ODMtNTJlYy00OGJmLThkZGItZTk2ZDM3YmM4ODQ5IiwidCI6ImVhOGJkMWZkLWFjMzQtNGFlMi1iNDIxLTZjZmEyZmNmZjI0MyJ9