Count of occurences outside of date range

[mazwro]

I have a table:

- Incident ID

- Date incident was opened

- Date incident was closed

I also have a slicer showig months

This links to my dim-date table on Dim_date.Date = date incident opened.

I want to calculate a number of Incidets which were opened BEFORE the month selected and they must have been closed AFTER the month selected.

Please help to point out where I am going wrong:

Opened Prior to Period Not closed = 
VAR SelectedStartDate = MIN(dim_date[Date]) -- Start date from slicer
VAR SelectedEndDate = MAX(dim_date[Date]) -- End date from slicer
RETURN
CALCULATE(
DISTINCTCOUNT(vw_PM_Dashboard[IDINC]),
vw_PM_Dashboard[Created DATE] < SelectedStartDate && vw_PM_Dashboard[Closed DATE] > SelectedEndDate,
REMOVEFILTERS(dim_date[Date]) -- Ignore slicer filters on the date dimension
)

[danextian]

Hi @mazwro 

Try the following:

Opened Prior to Period Not closed =
VAR SelectedStartDate =
    MIN ( Dates[Date] ) -- Start date from slicer
VAR SelectedEndDate =
    MAX ( Dates[Date] ) -- End date from slicer
VAR test =
    CALCULATE (
        [Total Revenue],
        FILTER (
            ALL ( Dates ),
            Dates[Date] < SelectedStartDate
                || Dates[Date] > SelectedEndDate
        )
    )
VAR test2 =
    CALCULATE (
        [Total Revenue],
        FILTER ( ALL ( Dates ), Dates[Date] < SelectedStartDate )
    )
        + CALCULATE (
            [Total Revenue],
            FILTER ( ALL ( Dates ), Dates[Date] > SelectedEndDate )
        )
RETURN
    test2

Both test and test2 variables should return the same value.

[shafiz_p]

Hi @mazwro Try this:

OpenedPriorToPeriodNotClosed = 
VAR SelectedStartDate = MIN(dim_date[Date]) -- Start date from slicer
VAR SelectedEndDate = MAX(dim_date[Date]) -- End date from slicer
RETURN
CALCULATE(
    DISTINCTCOUNT(vw_PM_Dashboard[IDINC]),
    vw_PM_Dashboard[Created DATE] < SelectedStartDate,
    vw_PM_Dashboard[Closed DATE] > SelectedEndDate,
    REMOVEFILTERS(dim_date[Date]) -- Ignore slicer filters on the date dimension
)

The main issue with your original code was the way the conditions were combined within the CALCULATE function. The conditions should be separated by commas rather than combined with &&. 

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[mazwro]

Hello @shafiz_p Thanks for your help, unfortunately I already tried this version and it only gives me blank card and I have no clue why:
What could be the reason for this?

[shafiz_p · Accepted solution]

Don't know much about your data and model. Without representative data hard to give exact solution. You could try troubleshooting your code that

1. The date range selected in the slicer actually includes dates that meet the conditions. If the selected date range does not cover any incidents that were opened before the start date and closed after the end date, the result will be blank.

2. Verify that the date columns (Created DATE and Closed DATE) are of the correct data type (Date). If they are stored as text, the comparison might not work as expected.

Create a temporary measure to check the values of SelectedStartDate and SelectedEndDate:

SelectedDatesCheck = 
VAR SelectedStartDate = MIN(dim_date[Date])
VAR SelectedEndDate = MAX(dim_date[Date])
RETURN
"Start Date: " & FORMAT(SelectedStartDate, "YYYY-MM-DD") & " | End Date: " & FORMAT(SelectedEndDate, "YYYY-MM-DD")

Hope this helps!