Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
09-17-2020 03:50 AM
Objective: Period over Period Retention is a comparison of one period vs another period. These periods are heterogeneous. The definition of the new retained and lost customers is only based on 2 periods of data i.e. like the customer did not visit in the last 3 months but his last visit was 12 months before. Basically, he did not come in 9 months before the above 3-month period.
Rolling 12 = CALCULATE([Sales],DATESINPERIOD('Date'[Date],ENDOFMONTH('Date'[Date]),-12,MONTH))
Rolling 3 = CALCULATE([Sales],DATESINPERIOD('Date'[Date],ENDOFMONTH('Date'[Date]),-3,MONTH))
Rolling 9 before three = CALCULATE([Sales],DATESINPERIOD('Date'[Date],eomonth(max('Date'[Date]),-3),-9,MONTH))
Lost Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([Rolling 3]) && not(ISBLANK([Rolling 9 before three])) , 1,BLANK()))
New Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([Rolling 9 before three]) && not(ISBLANK([Rolling 3])) , 1,BLANK()))
Retained Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(not(ISBLANK([Rolling 3])) && not(ISBLANK([Rolling 9 before three])) , 1,BLANK()))
eyJrIjoiNjljM2E3ODMtNTJlYy00OGJmLThkZGItZTk2ZDM3YmM4ODQ5IiwidCI6ImVhOGJkMWZkLWFjMzQtNGFlMi1iNDIxLTZjZmEyZmNmZjI0MyJ9