Showing results for 
Search instead for 
Did you mean: 

Customer Retention Part 2: Period over Period Retention


The 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.


This Period: Last 3 months rolling

Last/ previous period: Last 9 Months before the above 3 months


For e.g.: Consider the current month-year as September-2020. The current period or THIS PERIOD is 3 months i.e. June 2020 –August 2020 and the LAST PERIOD or previous period will be September 2019-May 2020.


New: Arrival of a customer for the first time in this period (here, the Last 3-month is considered as one period) is a new customer. Here, we won’t get any sales data from this customer for the last period (i.e. 9-month before this period (3-month)).


Lost: If a customer had visited the store last period (in last 12-months other than last 3-month) but did not visit in this period of 3-month, then we call him a Lost customer. This means that in this period there was no sale from this customer. However, there was a sale recorded by the same customer the previous period.


Retain: The customer who visited both periods (i.e. Last Period and This period). There will be sales data of this customer for this period and last Period.



We have 4 tables – Sales, Customer, Geography and Item

Screenshot 2020-09-10 18.17.56.pngScreenshot 2020-09-10 18.17.59.pngScreenshot 2020-09-10 18.18.01.pngScreenshot 2020-09-10 18.18.09.png


We also created a Date table in Power BI with Month, Qtr and Year information. Joined all of them.


Screenshot 2020-09-10 18.18.32.png



  • 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


Using Time Intelligence, we will create the following:  


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()))


Screenshot 2020-09-17 15.59.33.pngScreenshot 2020-09-17 15.59.40.png


Let us know what you think about these measures. Share your thoughts on Customer Retention Part 2.


You can get all my posts at


The file can found at:


What is your favorite Power BI feature release for September 2023?