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 ear...
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.

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

 

 

  • vasantha's avatar
    vasantha
    Microsoft Employee
    6 years ago

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