Help Creating a Count Measure

[AbhinavJoshi]

Hi,

I am looking to get a count of accounts where election date would be in next month period. Also, I have a coverage date range slicer so that the user can select the date range.
I have the following formula but it is not working correctly

NextMonthReenrollments =
VAR SelectedStartDate = MIN('Accounts'[Coverage Election])
VAR SelectedEndDate = MAX('Accounts'[Coverage Election])

VAR NextMonthStart = EOMONTH(TODAY(), 0) + 1
VAR NextMonthEnd = EOMONTH(TODAY(), 1)

VAR Result =
CALCULATE(
    COUNTROWS('Accounts'),
    FILTER(
        'Accounts',
        'Accounts'[Coverage Election] >= NextMonthStart &&
        'Accounts'[Coverage Election] <= NextMonthEnd &&
        'Accounts'[Coverage Election] >= SelectedStartDate &&
        'Accounts'[Coverage Election] <= SelectedEndDate
    )
)

RETURN
IF(ISBLANK(Result), 0, Result)

Sample data

160	Account 1	2025-01-01
869	Account 2	2024-11-19
871	Account 3	2026-06-01
872	Account 4	2024-11-01
1153	Account 5	2025-01-01
1313	Account 6	2024-11-01
1312	Account 7	2024-11-01
1235	Account 8	2024-11-01
1236	Account 9	2024-11-01
1242	Account 10	2025-01-01
1245	Account 11	2025-01-01
1260	Account 12	2025-04-02
1395	Account 13	2025-01-01
1396	Account 14	2025-01-01
1397	Account 15	2025-01-01
1399	Account 16	2026-01-01
1400	Account 17	2026-01-01
1401	Account 18	2025-01-01
1402	Account 19	2025-01-01
1398	Account 20	2025-01-01
1403	Account 21	2025-01-01
1432	Account 22	2025-01-01
1443	Account 23	2025-01-01
1577	Account 24	2025-01-01
1565	Account 25	2025-01-01
1566	Account 26	2025-01-01
1567	Account 27	2025-10-01
1591	Account 28	2026-01-01
1593	Account 29	2025-01-01
1599	Account 30	2025-01-01
1602	Account 31	2026-02-01
2057	Account 32	2026-06-01
2060	Account 33	2025-01-01
2071	Account 34	2024-11-01
2091	Account 35	2025-01-01
2173	Account 36	2025-01-01
2164	Account 37	2025-04-01
2165	Account 38	2025-01-01
2177	Account 39	2025-01-01
2230	Account 40	2025-01-01
2242	Account 41	2025-01-01
2227	Account 42	2025-01-01
2233	Account 43	2025-01-01
2226	Account 44	2025-01-01
2239	Account 45	2025-01-01
2228	Account 46	2025-01-01
2234	Account 47	2025-01-01
2238	Account 48	2025-01-01
2235	Account 49	2025-01-01
2237	Account 50	2025-01-01
2236	Account 51	2025-01-01
2272	Account 52	2025-01-01
2282	Account 53	2025-07-01

Sample slicer

Thank you in advance for helping.

[Anonymous · Accepted solution]

Hi @AbhinavJoshi ,

When the slicer's date range is from 2025-03-01 to 2026-05-01, it is correct to return the number of account numbers as 0. Because according to your formula, it will return the below values for the following variables in your formula. 

Variable	Value
SelectedStartDate	2025-03-01
SelectedEndDate	2026-05-01
NextMonthStart	2024-11-01
NextMonthEnd	2024-11-30

 And the conditions will changed as below. The two conditions in red are in conflict, and there is no account in the table that meets both conditions. Therefore, the number of accounts returns 0.

'Accounts'[Coverage Election] >= "2024-11-01" &&
'Accounts'[Coverage Election] <= "2024-11-30" &&
'Accounts'[Coverage Election] >= "2025-03-01" &&
'Accounts'[Coverage Election] <= "2026-05-01"

NextMonthReenrollments =
VAR SelectedStartDate = MIN('Accounts'[Coverage Election])
VAR SelectedEndDate = MAX('Accounts'[Coverage Election])

VAR NextMonthStart = EOMONTH(TODAY(), 0) + 1
VAR NextMonthEnd = EOMONTH(TODAY(), 1)

VAR Result =
CALCULATE(
    COUNTROWS('Accounts'),
    FILTER(
        'Accounts',
        'Accounts'[Coverage Election] >= NextMonthStart &&
        'Accounts'[Coverage Election] <= NextMonthEnd &&
        'Accounts'[Coverage Election] >= SelectedStartDate &&
        'Accounts'[Coverage Election] <= SelectedEndDate
    )
)

RETURN
IF(ISBLANK(Result), 0, Result)

Best Regards

[AbhinavJoshi]

Here

[hnguy71]

so based on your sample data from your original post, seems your result is all data from November 2024 all the way until October 2026.  Your earliest data point is only November 19, 2024 so getting all but 3 records seems correct.

Is that what you're looking for? If not, you'll need to make some tweaks to one or more of your dates.

[AbhinavJoshi]

Hi,

It removed all the filters and gave me a count of all accounts withouth respecting any filters from date slilcer.

[hnguy71]

Hi @AbhinavJoshi 

Can you replace your last line with this?

SelectedStartDate & " - " & SelectedEndDate & " | " & NextMonthStart & " - " & NextMonthEnd 

Then share with me your results. 

[hnguy71]

Hi @AbhinavJoshi ,

Because your next month data is currently out of range in context, you'll need to add an ALL to remove filter context. Try this instead:

NextMonthReenrollments =
VAR SelectedStartDate = MIN('Accounts'[Coverage Election])
VAR SelectedEndDate = MAX('Accounts'[Coverage Election])

VAR NextMonthStart = EOMONTH(TODAY(), 0) + 1
VAR NextMonthEnd = EOMONTH(TODAY(), 1)

VAR Result =
CALCULATE(
    COUNTROWS('Accounts'),
    FILTER(
        ALL('Accounts'),
        'Accounts'[Coverage Election] >= NextMonthStart &&
        'Accounts'[Coverage Election] <= NextMonthEnd ||
        'Accounts'[Coverage Election] >= SelectedStartDate &&
        'Accounts'[Coverage Election] <= SelectedEndDate
    )
)

RETURN
IF(ISBLANK(Result), 0, Result)

I also updated one of your "and" to "or" to pick up both current selection and next month as well.