Forum Discussion

gopalv's avatar
gopalv
Microsoft Employee
6 years ago
Solved

Earliest response time GitHub issue

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 numberdate last updated
447111/12/2019 8:00:16 PM +00:00
40928/8/2019 12:46:38 AM +00:00
40928/8/2019 12:58:38 AM +00:00
40928/8/2019 1:46:38 AM +00:00
38306/5/2019 6:47:32 PM +00:00
38306/5/2019 6:57:32 PM +00:00
75712/12/2019 6:06:14 PM +00:00

 

 

The expected output would therefore be:

 

issue numbertime to first response (datetime)
4471180 days
409212 minutes
383010 minutes
75730 days
 
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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's avatar
      vasantha
      Microsoft Employee
      6 years ago

      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")