Forum Discussion
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 chang...
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
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