Determining New Customers

Occasional Contributor

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!

7 Replies

@alpertakci I would use Power Query for such a task. See if the attached solution works for you.


best response confirmed by alpertakci (Occasional Contributor)


Assuming I understood, with Excel 2021 or 365:


in K5:

  uCustomers, UNIQUE(Table1[Customer]),
  FILTER(uCustomers, MINIFS(Table1[Active Since],Table1[Customer],uCustomers) >= EOMONTH(TODAY(),-1)+1)




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


Thanks @Riny_van_Eekelen! It does work fine, but I am going to go with the formula solution suggested by @L z.
Thank you for the additional explanation, @L z.
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