Countif based on date ranges

[Daniel_K]

Hello,

I've got a dataset of accounts which should be billed on either a monthly or quarterly basis (M/Q) and I need to establish if they're billed up to date or not up to date and then summarise the results to show the number and % not up to date and up to date regardless of the billing frequency.

We consider a monthly billed account up to date if the latest billed date is less than 46 days since todays date.

We consider a quarterly billed account up to date if the latest billed date is less than 155 days since todays date.

The attached dataset contains 25 lines (excluding the header line) but I only want the formula to bring back one instance of the account so I know the expected results are

20 individual accounts

14 up to date (70%)

6 not up to date (30%)

I just can't get the expression to work correctly!

Thank you in advance for your help

[Nicole-Smith · Accepted solution]

20 individual accounts:
count(distinct Account)

14 up-to-date accounts:
count({<[Billing Frequency]={'M'}, [Latest Billed Date]={">$(=today()-46)"}>}distinct Account) +
count({<[Billing Frequency]={'Q'}, [Latest Billed Date]={">$(=today()-155)"}>}distinct Account)

6 not up-to-date accounts:
count(distinct Account) - 
(count({<[Billing Frequency]={'M'}, [Latest Billed Date]={">$(=today()-46)"}>}distinct Account) +
count({<[Billing Frequency]={'Q'}, [Latest Billed Date]={">$(=today()-155)"}>}distinct Account))