DAX sum filter dates.

[Anonymous]

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 

Case 1 = if(calculate(sum('Products'[Units]),filter('Products',Products'[Manufacturer]="Banana"),DATESINPERIOD('Products'[Date],max(Products'[Date]),-2,year))>0,1)

but keep getting wrong.

Please help me on this. Thank you.

[stevedep]

@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:

[Anonymous]

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.

[stevedep]

Case 1 seems to have a strange meaning with respect to retention?

[Anonymous]

I think we use case 1 for calculating loyalty % of customers later.

[harshnathani]

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)

[Anonymous]

Good resource, but did not solved my question.