Combining Contains() & Datesbetween()

[henryvu93]

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

[Anonymous]

@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

[Anonymous]

---

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

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