Jira Dashboard (Created vs Resolved)

[Anonymous]

Hi,

Im trying to created a Staked Column chart which gives me Created vs Resolved tickets on JIRA for the last 30 days. Though I have managed to create everything (Attached). The query I have is I want to show Created tickets in the last 30 days which are Unresolved. How do I acheive this result?

On Jira it calcualtes Created Issues (Unresolved). So when i hover over the 8th March red bar it shows me 4 issues out of 9 issues created are Unresolved state (To Do, In-Progress, UAT etc)

This is from JIRA and it automatically calculates based on the projects and factors as last 30 days I want to create a similar one on PBI. I have already connected my data through OData This is the one that I created. Everything is fine but just that as of now its showing created Issues. I wanted to see Created Issues that are Unresolved just like the image of JIRA shows

In the above analysis I did on PBI. I have everything except that it shows created date and not the Created Issues (Unresolved) ones. How can I achieve this?

[v-yingjl]

Hi @Anonymous ,

Based on your descriptions and pictures, just create a measure like this to get the count of unsolved issues:

Unresolved = SUM('Table'[Jira Created]) - SUM('Table'[Jira Resolved])

Set a page level filter for the last 30 days and use a bar chart to show the result:

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks a ton for your solution. I have tried this everything works but when I pull the Unresolved measure to the values field nothing happens. Can you help?

Also, I tried opening your file shared but my PBD is not latest version it says.

[v-yingjl]

Hi @Anonymous ,

Try to use the latest version of power bi desktop to open it.

In addition, has your table looked like this:

This table is created manually by myself based on your pictures since I have not connected to Jira, but the calulation logic should be the same. 

If it still not works, you can consider sharing the structure of tables in your report like the above posted.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

Thanks for your prompt response. There might be a miss from my end but in my case JIRA created and Resolved are DATES.

Below is the screenshot

Key :- the JIRA tickets ID

The Created (MM/dd) is the one I created just to remove the time.

Kindly let me know if you need any further information

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Sorry for replying late. Seems like you need a Date table first, create a calculated table like this:

Calendar = CALENDARAUTO()

Create realationships between the Date table and your source table:

resolve-date is active, created-date is inactive

Create measures like this:

Resolved number = 
COUNTX (
    FILTER (
        'Table',
        'Table'[Created] <> BLANK ()
            && 'Table'[Resolved] <> BLANK ()
    ),
    [Key]
)

Unresolved number = 
CALCULATE (
    COUNT ( 'Table'[Key] ),
    'Table'[Resolved] = BLANK (),
    USERELATIONSHIP ( 'Table'[Created], 'Calendar'[Date] )
)

Add the filter conditions as your needed.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hey,

Thanks for your support on my above queries.

Would it be possible for you to take a look at the Average Age of Unresolved issues which I have shared below. Thanks in advance

[Anonymous]

Hi,

Im trying to a similar one for the Average Age of Unresolved issues. attached screenshot

 Can you please here? Let me know if you need any additonal infor\

[Anonymous]

Thanks a ton this worked well. Really appreciate your prompt help and response on the same. Hope I can get in touch with you for many other queries I face in future.