Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Good resource, but did not solved my question. 

  • stevedep's avatar
    stevedep
    Memorable Member
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        stevedep
        Memorable Member
        6 years ago

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