Clients newer than 6 months with average monthly turnover of under 50$

[avantime]

Hi all,

I need help with a formula to show me all clients newer than 6 months that have an average monthly turnover of under 50$.

Right now I have:

=count({1}distinct {<[Client name]={"=sum(Turnover)<50"}, [Client add date]={"=[Client add date]>=date(monthstart(today(),-6))"}>} [Client name])

but obviously I don`t get the desired results as it shows me all clients newer than 6 months with a total turnover less than 50 in this period.

Thank you!

[sunny_talwar]

For the sample provided what is the expected count of clients you are thinking to get?

[avantime]

168 clients, for new clients since February.

See attached Excel reference file. Hope it gives you the idea.

[avantime]

I think I got it

=Count(DISTINCT {1<[Client name]={"=Sum({1<[Client add date] = {'>=$(=Date(MonthStart(Today(),-6)))'}>}Turnover)

/ Count(DISTINCT {1<[Client add date] = {'>=$(=Date(MonthStart(Today(),-6)))'}>}[INVOICE MONTH]) < 50"}, [Client add date] = {'>=$(=Date(MonthStart(Today(),-6)))'}>} [Client name])

[sunny_talwar]

Awesome

[avantime]

Thank you for your guidance!

[sunny_talwar]

No problem at all Catalin