Forum Discussion

cearly65's avatar
cearly65
Frequent Visitor
4 years ago
Solved

Filterable Unresolved Issues Chart for JIRA data

I am trying to create a line chart showing unresolved ticket count by date over time for my team's Jira project.  All I have been able to find via searching the forums is this 'Created VS Resolved' c...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi cearly65 ,

     

    Please try the measure.

     

    Open tickets = 
CALCULATE (
    DISTINCTCOUNT ( 'Jiradata'[Issue Key] ),
    FILTER (
        'Jiradata',
        'Jiradata'[Created].[Date] <= MAX ( 'Calendar'[Date] )
            && (
                'Jiradata'[Resolved].[Date] >= MAX ( 'Calendar'[Date] )
                    || Jiradata[Resolved] = BLANK ()
            )
    )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

speedramps's avatar
speedramps
Super User
4 years ago

Hi again cearly65

 

Create a detached Calender table with contiguous dates and no relationships.

 

Create this measure

 

Open tickets =
// get reporting date
VAR mydate = SELECTEDVALUE('Calendar'[Date])
// create a subset of just tickets still open at close of play on the reporting day (this will exclude tickets open and closed same day)
VAR mysubset =
FILTER(Jira,
Jira[Create] <= mydate &&
Jira[Resolve] > mydate
)
 
RETURN
COUNTROWS(mysubset)
In your line graph drag the calander date to X-axis and the Open tickets measure to your y-axis. This does what you requested,
Now drag Assignee to the legend. This shows who has the most open tickers.
 
Click the thumbs up and accept as solution.
One problem per ticket please. If you have more questions about Jira reports then kindly please accept this solution and raise more tickets. You will get a better response and each solver each get kudos. Thank you. ğŸ˜€
cearly65's avatar
cearly65
Frequent Visitor
4 years ago

Hi speedramps,

 

I implemented what you recommended but the resulting chart doesn't seem to plot what I'm looking for.  I think it's plotting the change in unresolved by date and not the total count of unresolved by date.  Below is the screenshot of what the chart with your measure looks like.  You can compare it to what I posted earlier this morning as the desired chart.

 

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi cearly65 ,

     

    Please try the measure.

     

    Open tickets = 
CALCULATE (
    DISTINCTCOUNT ( 'Jiradata'[Issue Key] ),
    FILTER (
        'Jiradata',
        'Jiradata'[Created].[Date] <= MAX ( 'Calendar'[Date] )
            && (
                'Jiradata'[Resolved].[Date] >= MAX ( 'Calendar'[Date] )
                    || Jiradata[Resolved] = BLANK ()
            )
    )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • cearly65's avatar
      cearly65
      Frequent Visitor
      4 years ago

      That worked perfectly!  I had some issues initially, but realized that my date columns weren't formatted correctly for the formula.  Once that was fixed then the formula is showing the desired information.