Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Jira Data Visualization in Microsoft PowerBi - How to Find Average Time in Status

Hello - 

I have been attempting to visualize my Jira data in Microsoft PowerBi. With access to the database I have been able to establish the appropriate relationships with the Jira tables and make decent graphs, charts, etc. in Microsoft PowerBi.

Specifically, I am trying to calculate the average time Issues remain in certain statuses within my project. I have done Jira database research on this and understand that time in status is not naturally stored in the database tables. I understand that this information can be calculated by creating relationships between the jiraissues, changeitem, and changegroup tables.

I have successfully established the relationships between the 3 tables. In PowerBi I have successfully created a visual table that shows every Issue in the project with the date it went from one status to the next status, and so on. All Issues are shown transitioning from status to status as well as the date for which the transitions happened.

What I am not understanding, via PowerBi, is how to perform the calculations (subtraction) between the dates for which the transitions happened. My thinking is that if I can begin to calculate the difference between each Issue transition dates then I can begin to figure out the average time Issues have been in statuses.

 

The whole situation is becoming complicated very quickly. If anyone has any experience with Microsoft PowerBi or visualizing Jira data in general I would really, really appreciate it.

2 answers

Hi,

If you will be willing to try a third party add-on to get this information, please try our app - AIO Power BI Connector for Jira.

The app provides Time in Status values out of the box. 

It is the #1 selling Power BI connector for Jira on the marketplace and is used by thousands of organizations world wide, including several fortune 500 companies.

It is available for all the  three platforms Cloud/Server/Data Center.

Link to marketplace app page: https://marketplace.atlassian.com/apps/1219072/aio-power-bi-connector-for-jira

Documentation link: https://aioreports.atlassian.net/wiki/spaces/APBCFJ/overview

For any questions or issues, please email us at support@aioreports.com or log a ticket at http://support.aioreports.com/

Regards

AIO Support

Hello. Thanks for your response. Your add-on sounds very powerful, however, I am in no position to pay for 3rd party support at this point. I was hoping to find some means within the database tables in order to track on this information.

Hello @MAG-II 

As an alternative solution for visualization time data for each .issue, you can try Time in Status for Jira Cloud and its
Average time report. It shows the average time an issue has been spending in a particular status. For visualization select Chart view and you will be able to monitor data as pie, bar or area charts.average time.png

I hope, it helps

Best regards

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Service Management

JSM June Challenge #2: Share how your business teams became ITSM rockstars

For JSM June Challenge #2, share how your non-technical teams like HR, legal, marketing, finance, and beyond started using Jira Service Management! Tell us: Did they ask to start using it or...

203 views 6 7
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you