Identify Recurring client (a sale in each quarter)

[sbenzaquen]

Dear team,

I need your help on the following - I'm working on a sales data covering from Jan-20 to June-21, and I'd like to identify the clients that are a "Recurring client" which is defined as a client that has had at least one invoice/sale in each quarter for at least 4 out of the 6 quarters in the period.

For reference, I have a table with the sales data, a table with the list of clients and a calendar table. 

How can I do this?

I initially thought to create a column in the list of clients table, but not sure which formulas are appropriate.

Thanks,
Salvador

[sbenzaquen · Accepted solution]

Thanks for the interest. I found the answer myself in the end. Here is the solution...

Recurring debtor =

IF(

IF(CALCULATE(COUNT('Summary TRF SCF'[Seller/Supplier Name]),Calendar1[QuarterInCalendar]="Q1 2020")>=1,1,0)+

IF(CALCULATE(COUNT('Summary TRF SCF'[Seller/Supplier Name]),Calendar1[QuarterInCalendar]="Q2 2020")>=1,1,0)+

IF(CALCULATE(COUNT('Summary TRF SCF'[Seller/Supplier Name]),Calendar1[QuarterInCalendar]="Q3 2020")>=1,1,0)+

IF(CALCULATE(COUNT('Summary TRF SCF'[Seller/Supplier Name]),Calendar1[QuarterInCalendar]="Q4 2020")>=1,1,0)+

IF(CALCULATE(COUNT('Summary TRF SCF'[Seller/Supplier Name]),Calendar1[QuarterInCalendar]="Q1 2021")>=1,1,0)+

IF(CALCULATE(COUNT('Summary TRF SCF'[Seller/Supplier Name]),Calendar1[QuarterInCalendar]="Q2 2021")>=1,1,0)

>=4,"Y","N")

[vanessafvg]

can you share some sample data?