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

[amitchandak]

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.

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

Current Period: Last 3 Months

LTD: Time before 3 Month

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

Customer LTD vs Period.pbix