Forum Discussion

aaumond's avatar
aaumond
Frequent Visitor
8 years ago

Calculate TimeInStatus

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

Need Help

7 Replies

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

    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
  • pa's avatar
    pa
    Regular Visitor
    8 years ago

    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's avatar
      VivianX
      Frequent Visitor
      7 years ago

      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 

       

  • FlowViz's avatar
    FlowViz
    Helper III
    4 years ago

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