topic Re: DAX sum filter dates. in DAX Commands and Tips

DAX sum filter dates.

Anonymous — Mon, 22 Jun 2020 16:06:56 GMT

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.

Re: DAX sum filter dates.

Anonymous — Mon, 22 Jun 2020 16:13:14 GMT

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)

Re: DAX sum filter dates.

stevedep — Mon, 22 Jun 2020 18:51:50 GMT

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:

Re: DAX sum filter dates.

Anonymous — Mon, 22 Jun 2020 19:30:50 GMT

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.

Re: DAX sum filter dates.

stevedep — Mon, 22 Jun 2020 20:02:28 GMT

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

Re: DAX sum filter dates.

Anonymous — Mon, 22 Jun 2020 20:19:37 GMT

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

Re: DAX sum filter dates.

Anonymous — Mon, 22 Jun 2020 20:57:30 GMT

Good resource, but did not solved my question.