Creating custom column that calculate customer service response time for each ticket

[k943]

How do I create a column that look at each ticket and find the difference between ticket created date and ticket changed date which has to be the closest date from created date and when the person who changes is NOT the person who creates the ticket. 

For example:

ticket ID 1 is created by Dave on 12/20 and Laura is the one who changes it on 12/23, 12/24 and 12/25 but I only want the time when she first responds to the ticket which is 12/23. so the time it takes customer service (Laura) to respond to ticket is 3 days.

Same thing for ticket ID 2, Bob creates the ticket on 1/15/2023 and later changes it on 16th and 17th (maybe changes the description of the ticket) . Mark responds to it on 19th and 20th. Apply the same logic, the time it takes customer service (Mark) is 4 days (1/19 minus 1/15)

[k943]

Hi, I followed your formula but it results in blank for entire column

[Anonymous]

Hi @k943 ,

If the pbix file does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @k943 ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _1 =
    CALCULATE (
        MIN ( 'Table'[Changed date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Changed by] <> SELECTEDVALUE ( 'Table'[Created by] )
                && 'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] )
        )
    )
VAR _2 =
    DATEDIFF ( MAX ( 'Table'[Created date] ), _1, DAY )
RETURN
    _2

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[k943]

how to send a sample file? thanks a lot!