Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I want to get a retention rate of each customer. To get the retention rate, I need to check if the customer is qualified to the requirements that I've made.
Req 1 = customer must not buy any product from us or other companies for the last 24 months( or 2 years), and purchased our product in between 36th month and 24th month (in 3rd year).
Req 2 = Customer must not buy any of other companies's product for the last 36months and purchased our product every year during the last 36 months(3years).
The months counts from today's date. ex) 36 months from today is 2017/6/24
The data I have is shown below.
CUSTOMERID | DATE(Last purchased) | #units bought | manufacturer | Year |
XXXXX | 2015/02/12 | 1 | Ours | 2015 |
XXXXX | 2018/12/28 | 1 | competitor 1 | 2018 |
XXXXX | 2019/01/22 | 1 | competitor 2 | 2019 |
I'm new to power bi so if there is detail explanation, it would be appreciated.
Thank you.
Solved! Go to Solution.
Hi @Anonymous ,
Did you check my pbix file? If this doesn't help , please correct me and inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You could use measure to achieve this goal, you could refer to my sample for details. If this is not waht you want, please correct me .
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Could you provide me a example of how would you solve this?
Hi @Anonymous ,
Did you check my pbix file? If this doesn't help , please correct me and inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Do you want this done in Power Query or in DAX?
Either way is fine for me I just need a solution for this problem. I'm trying to use time intelligence dax function to solve this but still could not get this solve.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.