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: Prev1...
Need Help
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