Earliest response time GitHub issue

[gopalv]

I have data that looks like this, and I want to calculate the date to first response of an issue, by taking the difference between the 2nd earliest value in the "date last updated" column and the earliest value in the "date last updated" column, or the difference between today's date and the earliest value in the "date last updated," in case there is only one entry, for each issue in the "issue number" column. 

issue number	date last updated
4471	11/12/2019 8:00:16 PM +00:00
4092	8/8/2019 12:46:38 AM +00:00
4092	8/8/2019 12:58:38 AM +00:00
4092	8/8/2019 1:46:38 AM +00:00
3830	6/5/2019 6:47:32 PM +00:00
3830	6/5/2019 6:57:32 PM +00:00
757	12/12/2019 6:06:14 PM +00:00

The expected output would therefore be:

issue number	time to first response (datetime)
4471	180 days
4092	12 minutes
3830	10 minutes
757	30 days

[v-juanli-msft · Accepted solution]

Hi @gopalv 

Create measures

count =
CALCULATE (
    COUNT ( 'Table'[issue number] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[issue number] = MAX ( 'Table'[issue number] )
    )
)


earliest =
CALCULATE (
    MIN ( 'Table'[date last updated] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[issue number] = MAX ( 'Table'[issue number] )
    )
)


2nd earliest =
CALCULATE (
    MIN ( 'Table'[date last updated] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[issue number] = MAX ( 'Table'[issue number] )
            && [date last updated] > [earliest]
    )
)


difference time = DATEDIFF([earliest],[2nd earliest],MINUTE)

final =
SWITCH (
    TRUE (),
    COUNT ( 'Table'[issue number] ) = 1, DATEDIFF ( MAX ( 'Table'[date last updated] ), TODAY (), DAY ) & " days",
    COUNT ( 'Table'[issue number] ) > 1, [difference time] & " minutes"
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vasantha]

Hi,

Thanks for providing the information. I created all the measures mentioned above . I have created a new Github issue  and tried adding comments to the issue. Every time i add a comment to Github issue only 'earliest' measure value is updating but not '2nd earliest'. As per the requirement, 'earliest ' value should be updated only for the first time when ticket is updated followed by '2nd earliest'. Can you please look into it.

Below are the measures i created:

2nd earliest = CALCULATE (    MIN ( Issues[updated_at]),    FILTER ( ALLSELECTED ( Issues ),

        Issues[Issue number] = MAX ( Issues[Issue number]) && [updated_at] > [earliest] ))

count = CALCULATE (    COUNT ( Issues[Issue number] ),    FILTER (        ALLSELECTED ( Issues ),

        Issues[Issue number] = MAX ( Issues[Issue number] ) ))

difference time = DATEDIFF([earliest],[2nd earliest],MINUTE)

earliest = CALCULATE ( MIN ( Issues[updated_at] ), FILTER ( ALLSELECTED ( Issues ),

        Issues[Issue number] = MAX ( Issues[Issue number] )    ))

final = SWITCH (

    TRUE (),

    COUNT ( Issues[Issue number] ) = 1, DATEDIFF ( MAX ( Issues[updated_at] ), TODAY (), DAY ) & " days",

    COUNT ( Issues[Issue number] ) > 1, [difference time] & " minutes")

[vasantha]

@v-juanli-msft  : Can you please help me on this