Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Can you share how you obtained this data from Jira? I'm struggling with getting this data at the minute 😞
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.
Hi @aaumond
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" )
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" )
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |