Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX sum filter dates.

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

YearCUSTOMER IDDATEManufacturerUnits
2012

XXXXXXXX

03/04/2012

Apple1

2013

XXXXXXXX04/05/2013Banana1
2014XXXXXXXX01/05/2014Banana1
2015XXXXXXXX08/12/2015Watermelon1

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.
6 REPLIES 6
stevedep
Memorable Member
Memorable Member

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

retention.jpg

 

Anonymous
Not applicable

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?

Anonymous
Not applicable

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

harshnathani
Community Champion
Community Champion

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
Not applicable

Good resource, but did not solved my question. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors