Forum Discussion

AbhinavJoshi's avatar
AbhinavJoshi
Icon for Responsive Resident rankResponsive Resident
2 years ago
Solved

Help Creating a Count Measure

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 fo...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

hnguy71's avatar
hnguy71
Icon for Super User rankSuper User
2 years ago

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.