Calculate TimeInStatus

[aaumond]

Hi,

I retrieve data from my JIRA projects in PowerBI via the REST API services that JIRA provides.

On each ticket, I retrieve the history of the actions that took place, including status changes. For this case (status changes), I know the original status (Change.fromString), the final status (Change.toString) and the datetime of the status change.

So, the data is looks like this: 

From these data, I try to know the time spent in "Demande imprécise" status.

For example, if I consider the "11419" ID ticket, here are the periods of time in "Demande imprécise" status and the duration :

- 03/05/18 5:06 PM - 03/05/18 5:06 PM  ==> 14 s
- 03/05/18 5:34 PM - 03/05/18 5:37 PM ==> 3 m
- 03/05/18 5:38 PM - 03/05/18 5:40 PM ==> 2 m
Total: 5 m 14 s

I am a little lost to make theses operations, have you already realized that? If yes, how?

Tahnks in advance.

Best regards,

Anthony

[FlowViz]

Can you share how you obtained this data from Jira? I'm struggling with getting this data at the minute 😞

[ao352]

Try this https://community.atlassian.com/t5/Jira-questions/How-to-display-JIRA-quot-Time-In-Source-Status-quo...

[pa]

Hello,

i'm looking for this request. How do have make to retrieve the history of the actions, including status changes exactly like your screenshot ?

Thank you.

[VivianX]

I have some question as @pa 

How do have make to retrieve the history of the actions, including status changes exactly like your first screenshot ? @aaumond 

[v-juanli-msft]

Hi @aaumond

You could write calculated columns with DAX as follows step by step.

date ship down =
CALCULATE (
    MAX ( [Change date] ),
    FILTER (
        ALL ( Sheet1 ),
        [Index]
            = EARLIER ( [Index] ) - 1
            && MOD ( [Index], 2 ) = 0
            && [ID ticket] = EARLIER ( [ID ticket] )
    )
)

second =
IF (
    DATEDIFF ( [date ship down], [Change date], SECOND ) < 60,
    DATEDIFF ( [date ship down], [Change date], SECOND )
)

Column =
CONCATENATE (
    INT (
        MOD (
            CALCULATE (
                SUM ( [second] ),
                FILTER ( ALL ( Sheet1 ), [ID ticket] = EARLIER ( [ID ticket] ) )
            ),
            3600
        )
            / 60
    ),
    "m"
)
    & CONCATENATE (
        INT (
            MOD (
                MOD (
                    CALCULATE (
                        SUM ( [second] ),
                        FILTER ( ALL ( Sheet1 ), [ID ticket] = EARLIER ( [ID ticket] ) )
                    ),
                    3600
                ),
                60
            )
        ),
        "s"
    )

Or you can write a formula to calculate the total time period per ID ticket.

Column 2 =
VAR dateshipdown =
    CALCULATE (
        MAX ( [Change date] ),
        FILTER (
            ALL ( Sheet1 ),
            [Index]
                = EARLIER ( [Index] ) - 1
                && MOD ( [Index], 2 ) = 0
                && [ID ticket] = EARLIER ( [ID ticket] )
        )
    )
VAR second =
    IF (
        DATEDIFF ( [date ship down], [Change date], SECOND ) < 60,
        DATEDIFF ( [date ship down], [Change date], SECOND )
    )
RETURN
    CONCATENATE (
        INT (
            MOD (
                CALCULATE (
                    SUM ( [second] ),
                    FILTER ( ALL ( Sheet1 ), [ID ticket] = EARLIER ( [ID ticket] ) )
                ),
                3600
            )
                / 60
        ),
        "m"
    )
        & CONCATENATE (
            INT (
                MOD (
                    MOD (
                        CALCULATE (
                            SUM ( [second] ),
                            FILTER ( ALL ( Sheet1 ), [ID ticket] = EARLIER ( [ID ticket] ) )
                        ),
                        3600
                    ),
                    60
                )
            ),
            "s"
        )

Best Regards

Maggie

[Iamadityarajput]

Hi @v-juanli-msft ,

What's the logic behind index?

[aaumond]

Hi @v-juanli-msft,

Thanks for your reply.

Do you think it's possible to do this in Power Query rather than DAX ?

Regards,

Anthony