Forum Discussion
Determining New Customers
I have a list of customers serving different brands. Please see attached sample data. Each customer may be working with one or multiple brands. I am trying to determine which customers are new. The rule is that a supplier's active since date (and they may have more than one due to working with multiple brands) cannot be earlier than the current month.
Thank you in advance for your help!
Assuming I understood, with Excel 2021 or 365:
in K5:
=LET( uCustomers, UNIQUE(Table1[Customer]), FILTER(uCustomers, MINIFS(Table1[Active Since],Table1[Customer],uCustomers) >= EOMONTH(TODAY(),-1)+1) )
7 Replies
If you opt for Riny_van_Eekelen's PQ solution be aware you can simplify the Filtered Rows step with:
= Table.SelectRows(#"Grouped Rows", each Date.IsInCurrentMonth([GrpByCustomer]))...assuming your version of Excel supports function Date.IsInCurrentMonth
- Thank you for the additional explanation, Lorenzo
Assuming I understood, with Excel 2021 or 365:
in K5:
=LET( uCustomers, UNIQUE(Table1[Customer]), FILTER(uCustomers, MINIFS(Table1[Active Since],Table1[Customer],uCustomers) >= EOMONTH(TODAY(),-1)+1) )- This works super for me as long as my last_activity dates are later than the active_since dates, which, logically should be in that order. Thank you!
alpertakci Glad this helped & Thanks for providing feedback
alpertakci I would use Power Query for such a task. See if the attached solution works for you.
- Thanks Riny_van_Eekelen! It does work fine, but I am going to go with the formula solution suggested by Lorenzo
