Forum Discussion

henryvu93's avatar
henryvu93
Helper I
8 years ago

Combining Contains() & Datesbetween()

Hi guys, 

 

I have a sample table which show Active Customers based on Date below:

 

Here, if I want to count the number of customers who are still here after 1 year, the formula is:

Prev12M_Retained = VAR membersInPrevious12Month_datetable = CALCULATETABLE(Sample, SAMEPERIODLASTYEAR(Sample[Date])) 
return CALCULATE(DISTINCTCOUNT(Sample[Customer]), FILTER(Sample,CONTAINS(membersInPrevious12Month_datetable, Sample[Customer],Sample[Customer])))

Now, I want to count the number of customers who are still here over the last 3 months after 1 year, I tried this formula:

Prev12M_Retained_3M = VAR membersInPrevious12Month_datetable = CALCULATETABLE(Sample, SAMEPERIODLASTYEAR(Sample[Date])) 
return CALCULATE(DISTINCTCOUNT(Sample[Customer]), DATESBETWEEN(Sample[Date],Edate(min(Sample[Date]),-2),max(Sample[Date])),FILTER(Sample,CONTAINS(membersInPrevious12Month_datetable, Sample[Customer],Sample[Customer])))

However, it does not work and generate the same number as the previous formula.

 

Just wonder if anyone can help me regarding this? Thank you very much!

 

Regards,

Henry

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    henryvu93 wrote:

    Now, I want to count the number of customers who are still here over the last 3 months after 1 year 

    henryvu93,

    What result would you like to get for the above condition? I would recommend you create the following columns in your table, then determine the count number of customers by checking DIFF value \.

    PREDATE = CALCULATE(FIRSTNONBLANK('Sample'[Date],1),FILTER('Sample','Sample'[Customer]=EARLIER('Sample'[Customer])&&'Sample'[Date]<EARLIER('Sample'[Date])))
    DIFF = DATEDIFF('Sample'[PREDATE],'Sample'[Date],MONTH)



    Regards,
    Lydia

    • henryvu93's avatar
      henryvu93
      Helper I
      8 years ago

      Hi Lydia,

       

      Thanks for your reply. Basically, I want to count the number of customers who are still here after 1 year. For my first formula, It should give me 1 for 1/1/2018 as in Jan-18, there is customer C and 1 year ago there were A, B, C & D (C was one of the customers in Jan-17)

       

      For my second formula, I am looking through the last 3 months of every given date. So for 1/1/2018, I would look at 11/1/2017, 12/1/2017 & 1/1/2018 and there are 3 customers (A, B, C) and when I comparing these customers with those in Jan-17, it should give me 3 (A, B, C were 3 of the customers in Jan-17)

       

      Thanks!

      Henry

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    henryvu93,

    Create a count column using DAX below. And set its value to 1 in visual level filters.

    Count = IF('Sample'[DIFF]>=10 && 'Sample'[DIFF]<=12,1,0)



    Regards,
    Lydia