Calculate Ageing of closed incidents based on Incident closed date

[reddevil]

Hello Everyone, 

I have the below scenario where I want to calculate Time take to resolve incidents, I have calculated the measure as below,

I want to calculate the measure below based on date incident closed but I have the connected table to create date and hence all the dates are being filtered based on Create date, I want to find out how to calculate time take for resolved incidents based on Date incident closed. Your help would be useful on the above issue. Thanks 

Time taken for Resolved incidents = 
VAR AveDur =
    CALCULATE(AVERAGEX(Incidents,Incidents[Aged_Days_Incidents Closed_minutes] ),FILTER(ALL('Incidents'[Date incident closed].[Date]), Incidents[Date incident closed].[Date] <=MAX(Incidents[Date incident closed].[Date])))

VAR Days =
    INT ( AveDur / 60 / 24 )
VAR Hours =
    INT ( AveDur / 60 - Days * 24 )
VAR Minutes =
    ROUND ( MOD ( AveDur, 60 ), 0 )
VAR DaysText =
    IF ( Days >= 1, FORMAT ( Days, "General Number" ))
VAR HoursText =
    FORMAT ( Hours, "General Number" ) & "H"
VAR MinutesText =
    FORMAT ( Minutes, "General Number" ) & "M"
RETURN
     IF(VALUE(DaysText) = BLANK(), BLANK(), VALUE(DaysText))

[reddevil]

Thanks @BIDataRef @hnguy71, I have resolved it using the DC table as a slicer.

[BIDataRef]

Hi @reddevil 

• Create a separate Date table (dimension) rather than using the date column from your main table.
• Join the Incident table with Date table on Created Date and Closed Date (Only one relationship will be active).
• Most of the scenarios, the relationship with Created Date remains active and the Closed Date remains inactive.
• In order to calculate time taken using Closed Date, you need to use the USERRELATIONSHIPFUNCTION like this below -

VAR AveDur =
    CALCULATE(
            AVERAGEX(Incidents,Incidents[Aged_Days_Incidents Closed_minutes]),
            USERELATIONSHIP(Incidents[Date incident closed],DimDate[Date])
            )

Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Thanks

[Ashish_Mathur]

Hi,

Please share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[reddevil]

Hi Ashish,

Below is the sample data:

Status Name	Create Date	Date incident closed	Average
Closed	25/06/2024	11/07/2024
Closed	25/06/2024	11/07/2024
Closed	25/06/2024	11/07/2024
Closed	15/02/2024	27/08/2024
Closed	15/02/2024	20/06/2024
Result expected: Time taken to resolve incidents based on date incident closed
We do have fin year, Qtr Year and month year calcs

[hnguy71]

Hi @reddevil 

From your calculation, it doesn't seem like you're using any date table. I'm not sure what you mean by "connected"? Are you using a slicer on your page or on the filter pane using the created date to filter your data?

If that's the case, I would suggest that you do two things:

1. Set up a dedicated date table and build a relationship to your fact table. One ACTIVE to your created date and one INACTIVE to your closed date
2. Set up a disconnected table containing the same dates and use that as your slicer. The date(s) or period(s) retrieved from there should then be used to calculate both your created and closed expressions

[reddevil]

Hi @hnguy71 

I did try applying that but I am using just one filter as closed date from incidents table which is connected to the date table.

[hnguy71]

Hi @reddevil ,

Very similar to  @BIDataRef's response, you would need to adjust your expression to include a new function called USERELATIONSHIP. 

CALCULATE(
	AVERAGEX(
		Incidents,
		Incidents[Aged_Days_Incidents Closed_minutes]
		),
	FILTER(
		ALL('Incidents'[Date incident closed].[Date]), 
		Incidents[Date incident closed].[Date] <= MAX(Incidents[Date incident closed].[Date])
	), 
	USERELATIONSHIP(Incidents[Date incident closed].[Date], YOUR_DATE_TABLE[Date]) 
)

However, you may still have an issue because your slicer values are dependent on the "Created Date" rather than the dates from both created and closed dates. This is where I would recommend creating a separate disconnected table with all possible date values.

[reddevil]

Do you reckon I should use the disconnected table in the USERELATIONSHIP function.

[reddevil]

Hi @hnguy71 ,

Thanks for responding, yes i do have issues with create date slicer and I did create a seperate date table similar to the create date table. Do you reckon I should use that date table for slicer as I have other measures in the table which depends on create date slicer.

[reddevil]

Hi @hnguy71 

I have a date table connected to Incidents table using Create date. I have implemented the inactive relationship based on closed date to the date table. Also how do we create the measure for ageing for resolved incidents based on date closed.

[BIDataRef · Accepted solution]

Hi @reddevil 

You can use USERRELATIONSHIP function to calculate based on an inactive relationship. For E.g.

VAR AveDur =
    CALCULATE(
            AVERAGEX(Incidents,Incidents[Aged_Days_Incidents Closed_minutes]),
            USERELATIONSHIP(Incidents[Date incident closed],DimDate[Date])
            )

[reddevil]

Thanks @BIDataRef for the measure, I am having an issue with the create date slicer, i do get different results when i use the date incident closed dates slicer.

[BIDataRef]

@reddevil 

As already suggested by @hnguy71 , create a disconnected date table containing the same dates and then use them as a slicer.