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
Be the first to comment