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

[mstrick]

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!

[amitchandak · Accepted solution]

@mstrick , follow the approach of HT Analytics Current/Active employee

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

Files are attached

[mstrick]

@amitchandak This was perfect! Thank you for helping me and adding in the additional material!