cancel
Showing results for 
Search instead for 
Did you mean: 
amitchandak

Customer Retention Part 5: Year on Year Retention

Objective:

YoY Retention is only focused on Year vs Year. The definition of the new, retained, and lost customers are only based on 2 Years of data i.e. This Year and Last Year.

 

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

 

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

 

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

 

Implementation

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

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

 

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

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

amitchandak_0-1626631043724.png

 

amitchandak_1-1626631043740.png

 

amitchandak_2-1626631044581.png

 

amitchandak_3-1626631044502.png

 

 

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

amitchandak_4-1626631044553.png

 

 

Using Time Intelligence, we will create the following:  

                Abbreviations: YTD – Year to Date, LYTD – Last Year to Date

  • YTD Sales, LYTD Sales. This Year sales and last Year sales
  • New:  IF YTD is NOT Blank and LYTD is Blank
  • Lost:  IF LYTD is NOT Blank and YTD is Blank
  • Retain: IF LYTD is NOT Blank  and YTD is NOT Blank

 

 

 

YTD = calculate([Sales],datesytd('Date'[Date]))
LYTD = calculate([Sales],DATESYTD(DATEADD('Date'[Date],-1,MONTH)))	
Lost Customer This Month = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([YTD]) && not(ISBLANK([LYTD])) , 1,BLANK()))
New Customer This Month = sumx(VALUES(Customer[Customer Id]), if(ISBLANK([LYTD]) && not(ISBLANK([YTD])) ,1,BLANK()))
Retained Customer This Month = if(not(ISBLANK([YTD])) && not(ISBLANK([LYTD])) , 1,BLANK())

 

 

amitchandak_5-1626631044351.png

 

 

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

 

You can get all my posts at https://community.powerbi.com/t5/Data-Stories-Gallery/Blog-Analysis/m-p/1265567#M4403