Get count of projects with open date before the slicer and close date is blank or after the slicer

[suraetay]

Hi all, I've been wrestling with this for a while now and I'm constantly coming up empty. I have a table, cm_project_overview, that has two date fields, open_dt (open date) and close_dt (close date). There is an active relationship between the open_dt of this table and my Date table. I need to get a count of projects whose status is considered open, where the open_dt is before the first day of the slicer's range and the close_dt is either blank or after the last date of the slicer.

This visual has two slicers, one is the date range (pulled from the DATE table) and the other is for parent account (pulled from the cm_project_overview table).

I've tried a couple different DAX queries, but they're not returning the correct row count. Here are the ones I've tried most recently that feel like they might be close. Any help would be greatly appreciated! I did find this post that had the same issue, but I'm not entirely sure how the solution worked, as it seems really similar to the queries below.

ct_open_projs = 
CALCULATE(
COUNTROWS(cm_project_overview),
cm_project_overview[project_added_dt] < MIN('DATE'[Date]),
OR(ISBLANK(cm_project_overview[close_dt]),
cm_project_overview[close_dt] > MAX('DATE'[Date])),
ALL('DATE'))

var_open_proj = 
VAR min_dt = MIN('DATE'[Date])
VAR max_dt = MAX('DATE'[Date])

RETURN
    CALCULATE(
        COUNTROWS(cm_project_overview),
        FILTER(
            ALL(cm_project_overview),
        cm_project_overview[added_dt] < min_dt &&
        (ISBLANK(cm_project_overview[close_dt]) || cm_project_overview[close_dt] > max_dt)
        ),
        ALL('DATE')
    )

[ryan_mayu]

@suraetay 

maybe you can try this

Open Projects  =
VAR MinDate = MIN('DATE'[Date])
VAR MaxDate = MAX('DATE'[Date])
RETURN
CALCULATE(
COUNTROWS(cm_project_overview),
FILTER(
ALL(cm_project_overview),
cm_project_overview[open_dt] < MinDate &&
(
ISBLANK(cm_project_overview[close_dt]) ||
cm_project_overview[close_dt] > MaxDate
)
),
ALL('DATE')
)

if this does not work , pls provide the sample data and expected output

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached files.

Hope this helps.

[nielsvdc]

Check the measure that is marked as the solution. I have tested it succesfully on your active projects file.

Hope this helps. If so, please give a Kudos 👍 or mark as Accepted Solution ✔️.

[nielsvdc · Accepted solution]

Hi @suraetay,

Check the DAX code below. Why this should probably work, is that REMOVEFILTERS('DATE') prevents the active relationship from excluding rows before the custom logic is applied. The parent account slicer still applies.

var_open_proj =
// Get the minimum and maximum date from the selected date slicers
VAR min_dt = MINX(ALLSELECTED('DATE'[Date]), 'DATE'[Date])
VAR max_dt = MAXX(ALLSELECTED('DATE'[Date]), 'DATE'[Date])

RETURN
CALCULATE(
    COUNTROWS(
        FILTER(
            cm_project_overview,
            cm_project_overview[open_dt] < min_dt
                && ( ISBLANK(cm_project_overview[close_dt]) || cm_project_overview[close_dt] > max_dt )
        )
    ),
    // Remove date filters coming via the active relationship on open_dt,
    // but keep filters from cm_project_overview.
    REMOVEFILTERS('DATE')
)

Hope this helps. If so, please give a Kudos 👍 or mark as Accepted Solution ✔️.

[suraetay]

That worked perfectly, thank you so much!