Issuing with Calendar Table and two date fields on the same table

[kevin_sbi]

Good day, I am running into an issue which I believe is being caused by the fact that I have two date fields on one table and trying to use the date field on a calendar.   

We have an incident table that has a resolved date and a create date. We need to get a count of incidents resolved and incidents created during any given month.   I can get the create incidents number accurately using the calendar date.   For the number of resolved tickets, I only get accurate numbers when I used the resolved date from the incident calendar.   Ultimately, I need to get the different between these two numbers to get the increase/decrese of tickets per month, so I believe I need to be able to use the calendar date so the numbers are accurate.

We have an active relationship between the Calendar table and the create date on the incident table.
We have an inactive relationship between the Calendar table and the resolve date on the incident table.

I have a measure to count incidents as follows:

Count of Incidents (Non-SD1) = CALCULATE (COUNTROWS('service_now incident'), FILTER('Calendar', 'Calendar'[Date]>=DATE(2024,1,1)), FILTER('service_now incident', 'service_now incident'[assignment_group]<>"ITOPS - SD - Level 1" && CONTAINSSTRING('service_now incident'[assignment_group], "ITOPS")))

For incidents created, we are only pulling incidents created this year. This works correct and as expected. Based on this, I have a table that shows the number of incidents created by month. Here is the report:

Month year sort is in the Calendar table as:

Month year Sort = format('Calendar'[Date],"YYYYMM")

For incidents resolved, we are wanting to pull all incidents resolved each month (even if they were created in the previous year):

Count of Resolved Incidents (Non-SD1) = CALCULATE (COUNTROWS('service_now incident'), USERELATIONSHIP('Calendar'[Date], 'service_now incident'[Resolved Short Date]), FILTER(ALL('service_now incident'), 'service_now incident'[Resolved Short Date]>=DATE(2024,1,1)), FILTER('service_now incident', 'service_now incident'[assignment_group]<>"ITOPS - SD - Level 1" && CONTAINSSTRING('service_now incident'[assignment_group], "ITOPS")))

Here is the report:

The number for January should be 3053

For testing purposes, I created another table using Resolved Month year sort that is defined as:

Resolved Month year Sort = format([Resolved Short Date],"YYYYMM")

This shows the correct number of 3053 for January.

Based on this and trying to look at what may be going on, I think it is related to the fact that I am using the Month Year Sort. In the resolved number, there doesn't seem to be any incidents that were created outside of the month it is calculating.   I have tried to use ALL to clear the relationship filter, and also used the removefilters.  

I need to use the calendar date though, as the next piece I need to do is calculate the difference between Opened and Resolved so I can't just use the dates from the incident table.  

Any idea on what may be going on?

[johnt75]

I think its the use of FILTER on the table as a whole. That's expanding out to the calendar table using the relationship which is active at the time, and that is the relationship with opened date.

Try

Count of Resolved Incidents (Non-SD1) =
CALCULATE (
    COUNTROWS ( 'service_now incident' ),
    USERELATIONSHIP ( 'Calendar'[Date], 'service_now incident'[Resolved Short Date] ),
    'service_now incident'[Resolved Short Date] >= DATE ( 2024, 1, 1 ),
    'service_now incident'[assignment_group] <> "ITOPS - SD - Level 1"
        && CONTAINSSTRING ( 'service_now incident'[assignment_group], "ITOPS" )
)

[kevin_sbi]

Thank you John!  This actually did resolve the issue in a table by itself and I created a single table and everything looks great!

Now, I am running into an issue when I use the resolved count to create a matrix with month year sort and assignment groups.  The numbers for a row are all the same, and it appears to be ignoring the conditions I had set (The 1- groups should not be showing up).   I tried plugging in just the Resolved Month year sort but same thing.