Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Jira Dashboard (Created vs Resolved)

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 ...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
v-yingjl
Icon for Community Support rankCommunity Support
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      v-yingjl
      Icon for Community Support rankCommunity Support
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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.