Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I'm trying to get rentention percentage of each customer ID, but there are 3 conditions that identify the customer is retained or not. (if any of these are appliable, then the customer is retained.)
case 1 is if customer purchased our company unit within 24months, the customer is retained.
case 2 is if customer did not purchased our product for last 23 months but bought our product on the 3rd year
case 3 if customer did not purchased our product for last 23 months but bought our product within the first three year.
My data is arranged like this :
Table Products
Year | CUSTOMER ID | DATE | Manufacturer | Units |
2012 | XXXXXXXX | 03/04/2012 | Apple | 1 |
2013 | XXXXXXXX | 04/05/2013 | Banana | 1 |
2014 | XXXXXXXX | 01/05/2014 | Banana | 1 |
2015 | XXXXXXXX | 08/12/2015 | Watermelon | 1 |
I need to create new columns about each cases next to the units column. If the customer is retained, then put 1, otherwise 0.
I've used
@Anonymous Shouldn't you be checking if they purchased something in the 2 year ahead to classify them as retained? In that case you need something like this?
Retention =
var _sv = SELECTEDVALUE(Products[DATE]) +1
return
IF(SELECTEDVALUE(Products[Manufacturer])="Banana"; //Only for our products, Banana;
IF( //Use the whole table, and then set some filters.
CALCULATE(SUM(Products[Units]);ALL(Products);Products[Manufacturer]="Banana";
DATESINPERIOD(Products[DATE];_sv;2;YEAR))
> 0 ; 1 ; 0))
As seen here:
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Let me revise what I posted. We consider
Case 1 = the customer is retained if the customer only bought our company's unit in last 2 years, the customer is retained
Case 2 = if the customer did not purchased any products (which means did not buy any other competing companies' product) for 2 years but bought our company's product on 3rd year, then the customer is retained.
Case 3 = if the customer did not purchased any other brands' product but bought our product for the last 3 years straight, then the customer is retained.
Case 1 seems to have a strange meaning with respect to retention?
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
I think we use case 1 for calculating loyalty % of customers later.
Hi @Anonymous ,
You can check this video for calculating the customers as per the criterias.
https://www.youtube.com/watch?v=cfDyNPQIJtA
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Good resource, but did not solved my question.
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |