if

[Anonymous]

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.

[dax]

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.

[Anonymous]

Could you provide me a example of how would you solve this?

[dax · Accepted 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.

[lbendlin]

Do you want this done in Power Query or in DAX?

[Anonymous]

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.