cancel
Showing results for
Did you mean:
aaumond Frequent Visitor

## 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?

Best regards,

Anthony

5 REPLIES 5 Community Support Team

## Re: Calculate TimeInStatus

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
aaumond Frequent Visitor

## Re: Calculate TimeInStatus

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

Regards,

Anthony

pa Visitor

## Re: Calculate TimeInStatus

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 Frequent Visitor

## Re: Calculate TimeInStatus

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

ao352 Frequent Visitor

## Re: Calculate TimeInStatus

Announcements   