Filterable Unresolved Issues Chart for JIRA data

[cearly65]

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' chart thread, but when I create the chart after setting up the data as shown in the accepted solution, I'm not getting the daily ticket counts plotted (see below image).

Ideally I would have a chart like pictured below which I can filter via other fields in the Jira data like issue type, program, owner, etc.  I can share the source data I'm working with if needed, but it's just your standard exported Jira data.  Any help would be greatly appreciated!

[cearly65]

So I figured out how to make the chart after much trial and error (DAX is a very familiar yet frustratingly alien language compared to Excel).  I had created a second calendar table per the 'Created VS Resolved' chart thread with a column formula of Calendar = CALENDAR(MIN('Jiradata'[Created]),TODAY()).  I then added the below formula as a measure (sorry for the crap formatting)

Unresolved =
CALCULATE (
    COUNT ( 'Jiradata'[Issue Key] ),
    FILTER (
        ALLSELECTED ( 'Jiradata' ),
        'Jiradata'[Created] <= MAX ( 'Calendar'[Date] )
    )
)
    - (
        CALCULATE (
            COUNT ( 'Jiradata'[Issue Key] ),
            FILTER (
                ALLSELECTED ( 'Jiradata' ),
                'Jiradata'[Resolved] <= MAX ( 'Calendar'[Date] )
            )
        )
            - CALCULATE (
                COUNT ( 'Jiradata'[Issue Key] ),
                FILTER ( ALLSELECTED ( 'Jiradata' ), 'Jiradata'[Resolved] = BLANK () )
            )
    )

So I'm assuming that formula is atrocious and can be written much simpler, but I also noticed that if I put a date slicer on the chart and change the start date then the chart gets really weird.

Functional chart before adjusting start date via slicer

Chart after adjusting start date

[Daryl-Lynch-Bzy]

Hi @cearly65 - I was wondering if we could firstly simplify the complexity by turning the DateTime value to Date Only in Power Query as you import the data.

The challenge you face is setting the MAX ( Date ) then using this date to perform the Row Context validation against the Created Date, Resolved Date.  When using a Slicer or Date in the y-axis it will update the MAX ( date ) value to the current selection.  Something like the following should work - sorry edit to include BLANK resolved dates).

Count Open Issue = 
VAR _date = MIN ( Calendar[Date] , TODAY() )
VAR _filter = FILTER ( Issues , Issue[Create Date] <= _date && ( Issue[Resolved Date] >= _date || ISBLANK(Issue[Resolved Date] ) )
RETURN
      COUNTROWS ( _filter )

This measure would return current open issues when it is not filtered by Date, or when trying to look at future dates.  When you start filtering for past dates, it should calculate the Issue effective open as at the filtered date.  The line chart will show changes each day, so you may want to consider filtering to show the beginning of week to highlight week over week changes rather than daily.

[cearly65]

I did find that simplifying the date columns to date helped a lot with my attempts to make charts.  As for your proposed formula, I updated the references to what's in my file (see below), but I get the following error: "A single value for column 'Date' in table 'Calendar Table' cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result"

Count Open Issue =

VAR _date = MIN ( 'Calendar Table'[Date], TODAY())

VAR _filter = FILTER ( RSDjiradata , RSDjiradata[Created] >= _date && RSDjiradata[Resolved] <= _date )

RETURN

COUNTROWS ( _filter )

[speedramps]

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]

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 · Accepted solution]

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]

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.

[speedramps]

Please share example data from all tables.

Please share as a table and not a screen shot so we can import and use it to create a demo solution.

Also share a example desire output with clear decsription of how unresolved and resolved tickers are determined.

And if a ttick is raised on 8/5/2021 and resolved on 2/5/2022. How do you want it displayed on 11/5/2021. It was unresolved then but it is resolved now.

[cearly65]

See sample data below.  I am essentially trying to create a chart that shows the count of unresolved issues (sum of created minus sum of resolved) for each day over time to see if we're trending towards more issues being created than we can resolve or trending towards resolving more than new ones are being created.  In your specific example the chart would count the ticket towards the unresolved total for 11/5/2021, then would no longer count it as unresolved in the total for 2/6/2022.

Once I have that chart working then I want to be able to filter it by different criteria in the jira data.  For example filtering Document vs Procedure issue types to see if we're resolving either type faster than new ones are being created.

Issue Key	Issue Type	Priority	Status Category	Assignee	Program	Created	Updated	Due Date	Resolved
RSD-9	Document	Not Prioritized	Done	Employee K		11/9/2020 11:17	12/9/2021 8:09	5/28/2021 0:00
RSD-567	Document	Not Prioritized	In Progress	Employee G		11/17/2020 10:56	4/25/2022 14:30	5/28/2021 0:00
RSD-568	Document	Critical	Done	Employee A		11/17/2020 11:05	4/15/2022 9:04	5/28/2021 0:00
RSD-569	Document	High	In Progress	Employee G		11/17/2020 11:35	4/21/2022 19:53	5/28/2021 0:00
RSD-664	Document	Not Prioritized	In Progress	Employee G		2/10/2021 8:05	11/23/2021 10:59		8/27/2021 6:28
RSD-665	Document	Not Prioritized	In Progress	Employee J		2/10/2021 8:30	4/26/2022 8:43
RSD-673	Document	High	In Progress	Employee G		2/24/2021 7:50	3/28/2022 15:34	3/24/2022 0:00
RSD-674	Document	Not Prioritized	In Progress	Employee G		2/24/2021 8:22	4/20/2022 16:23		8/27/2021 6:29
RSD-676	Document	Critical	In Progress	Employee G		3/1/2021 17:08	4/28/2022 7:21
RSD-680	Document	Not Prioritized	In Progress	Employee J	Program AB	3/10/2021 15:21	5/4/2022 16:35
RSD-683	Document	Not Prioritized	In Progress	Employee D		3/16/2021 16:04	3/21/2022 12:34
RSD-684	Document	Not Prioritized	In Progress	Employee D		3/17/2021 6:58	4/27/2022 11:04
RSD-688	Document	High	In Progress	Employee G		3/19/2021 18:47	4/12/2022 17:11
RSD-689	Document	Medium	Done	Employee A		3/19/2021 18:53	4/15/2022 9:06		8/27/2021 6:30
RSD-711	Document	Medium	In Progress	Employee J		4/6/2021 9:15	5/2/2022 14:41	6/1/2021 0:00	8/27/2021 6:32
RSD-747	Procedure	Medium	In Progress	Employee D	Program AB	5/6/2021 16:35	1/31/2022 14:48
RSD-749	Procedure		In Progress	Employee D	Program B	5/11/2021 17:47	4/27/2022 7:22
RSD-750	Procedure		Done	Employee G	Program AB	5/11/2021 17:47	12/23/2021 13:18		10/28/2021 12:12
RSD-751	Procedure		Done	Employee D	Program B	5/11/2021 17:47	4/4/2022 12:48		1/31/2022 16:47
RSD-752	Procedure		Done	Employee D	Program AB	5/11/2021 17:47	3/8/2022 8:36		12/29/2021 17:30
RSD-753	Procedure		Done	Employee D	Program AB	5/11/2021 17:47	4/4/2022 12:48		1/31/2022 16:55
RSD-754	Procedure		Done	Employee G	Program B	5/11/2021 17:47	3/7/2022 10:17		10/28/2021 12:30
RSD-755	Procedure		Done	Employee G	Program B	5/11/2021 17:47	3/8/2022 8:23		10/28/2021 10:09
RSD-756	Procedure		Done	Employee D	Program AB	5/11/2021 17:47	2/2/2022 14:51		1/13/2022 17:49
RSD-757	Procedure		In Progress	Employee D	Program AB	5/11/2021 17:47	3/8/2022 9:43
RSD-758	Procedure		Done	Employee G	Program AB	5/11/2021 17:47	3/8/2022 8:17		11/22/2021 14:29
RSD-759	Procedure		Done	Employee G	Program AB	5/11/2021 17:47	3/8/2022 8:42		11/8/2021 10:09
RSD-760	Procedure		Done	Employee H	Program AB	5/11/2021 17:47	4/25/2022 22:09		3/11/2022 9:12
RSD-761	Procedure		In Progress	Employee G	Program B	5/11/2021 17:47	8/31/2021 12:07
RSD-762	Procedure		Done	Employee G	Program B	5/11/2021 17:47	5/3/2022 10:33		5/3/2022 10:21
RSD-763	Procedure		Done	Employee D	Program B	5/11/2021 17:47	5/2/2022 12:09		12/29/2021 15:20