Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started


Customer Retention Part 1: Month on Month Retention

Using PowerBI, analyze customer behavior when making a purchase.



MoM Retention is only focused on Month vs Month. The definition of the new, retained, and lost customers are only based on 2 months of data i.e. This month and Last month.


New: Arrival of a customer for the first time this month is a new customer. Here, we won’t get any sales data from this customer for last month.


Lost: If a customer had visited the store last month but did not visit the preceding month (i.e. this month) then we call him a Lost customer. This means that in this month there was no sale from this customer. However, there was a sale recorded by the same customer the previous month.


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



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

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

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


Joined all tables with sales: 1 to Many (sales)

Screenshot 2020-09-10 18.18.32.png


Using Time Intelligence, we will create the following:  

                Abbreviations: MTD – Month to Date, LMTD – Last Month to Date

  • MTD Sales, LMTD Sales. This month sales and last month sales
  • New:  IF MTD is NOT Blank and LMTD is Blank
  • Lost:  IF LMTD is NOT Blank and MTD is Blank
  • Retain: IF LMTD is NOT Blank  and MTD is NOT Blank




MTD = calculate([Sales],datesmtd('Date'[Date]))
LMTD = calculate([Sales],DATESMTD(DATEADD('Date'[Date],-1,MONTH)))	
Lost Customer This Month = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([MTD]) && not(ISBLANK([LMTD])) , 1,BLANK()))
New Customer This Month = sumx(VALUES(Customer[Customer Id]), if(ISBLANK([LMTD]) && not(ISBLANK([MTD])) ,1,BLANK()))
Retained Customer This Month = if(not(ISBLANK([MTD])) && not(ISBLANK([LMTD])) , 1,BLANK())



Screenshot 2020-09-10 18.22.18.png


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


You can get all my posts at


The file is attached to this blog.