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

Customer Retention Part 5: LTD and PeriodYoY Retention is only focused on Year vs Year. The definiti

New: The arrival of a customer for the first time is a new customer. There are no sales before this period to this customer.

 

Lost: If a customer had visited the store in past and not visited in the given period 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 in the past.

 

Retain: The customer who visited in the past and in the given period too. There will be sales data of this customer in this period and in the past.

 

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-1633367102738.png

 

 

amitchandak_1-1633367102962.png

 

 

amitchandak_2-1633367103091.png

 

 

amitchandak_3-1633367103064.png

 

 

 

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

amitchandak_4-1633367102581.png

 

 

Current Period: Last 3 Months

LTD: Time before 3 Month

 

 

amitchandak_5-1633367473926.png

 

Mesures

 

 Rolling 3 = CALCULATE([Sales],DATESINPERIOD('Date'[Date],ENDOFMONTH('Date'[Date]),-3,MONTH))  
 
Rolling all before 3 = CALCULATE([Sales],filter(all('Date'), 'Date'[Date]<= EOMONTH(max('Date'[Date]),-3)) ) 
 

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

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

 

 

 

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

 

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