cancel
Showing results for
Did you mean:
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
Frequent Visitor

Re: Calculate TimeInStatus

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

Regards,

Anthony

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.

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

Frequent Visitor

Re: Calculate TimeInStatus

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,910)