Forum Discussion

mstrick's avatar
mstrick
Regular Visitor
2 years ago
Solved

Filtering Measure by Two Different Date Columns - Not matching manual filtering

Hello!
I am trying to create a count of incidents in our backlog at the end of each month using historical data. I want this to be dynamic as it will need to be updated each month with the new data. Logically, I am looking to see if there was an incident that was created before the end of the month and it is resolved after the end of the month. I currently have the following measure defined:

 

Backlog =
    CALCULATE(
        COUNTROWS('Incidents'),
        'Incidents'[Created] <= EOMONTH(SELECTEDVALUE('Calendar'[Month Year]),0),
        'Incidents'[Resolved] > EOMONTH(SELECTEDVALUE('Calendar'[Month Year]),0)
    )
 
My 'Incidents' table is connected to the 'Calendar' table using *:1 active relationship from the "Created" date column in 'Incidents' to "Date" column in 'Calendar'. I have created the "Month Year" column to be represented in the slicer.
 
Currently this measure is filtering the 'Incident' table, but showing a number that is less than what is expected. When I use the measure, I see a count of 87 Incidents however, when I manually filter the table to check, I see 149 incidents that follow the conditions I am expecting.
 In Visualizations Pane with testing filter for November:
Manually Filtering Data Table:

 

Any ideas as to why these numbers are not matching?

Is my measure incorrectly written OR is there something to do with the relationships in the model to the Calendar Table?

 

Any help is appreciated!

 

2 Replies