Average Bug age

[aayaffe]

When measuring a successful team one of the big measures I believe should be used is the amount of time a bug was open. It is a measure of how fast an issue is resolved for the customer.

It would be great if a specific graph of average open bug age would exist in this dashboard.

Lead time is quite close to it but it does not take into account the open issues and WIP only deals with items that are currently in progress. Furthermore currently there exist only one graph for all types of WIs

Thank you for a wonderful dashboard.

[nbrown02]

Thanks for sharing - I have moved this to the ideas section so as not to be conflated with bugs on the template :)

So, if I understand correctly, this is the date difference between when a bug was created (CreatedDate) and the current date? And this is for everything not in a completed state?

[aayaffe]

Seems exactly right

[nbrown02]

So the issue would be what would be your time element on your x-axis? At the minute, the dates table has a relationship with work items when they are started or completed. By including things that are open (and not started or completed) it becomes tricky visualizing this.

What you could do, if you just wanted a static 'number' would be:

1. Click 'Transform Data' and find your WorkItems In Progress2 table and adjust the 'Source' to included 'CreatedDate'
   

2. Hit 'Close & Apply' (top left)

3. Once loaded, in your WorkItems In Progress2 table, add in a column called TimeSinceCreated:
   TimeSinceCreated = DATEDIFF ( 'WorkItems In Progress2'[CreatedDate], TODAY (), DAY )

4. Create a new table called 'TimeOpen' with the following formula
   TimeBugIsOpen = UNION ( SELECTCOLUMNS ( FILTER('WorkItems Completed', [WorkItemType] = "Bug"), "WorkItemId", [WorkItemId], "WorkItemType", [WorkItemType], "LeadTimeOrOpen", [LeadTimeDays] ), SELECTCOLUMNS ( FILTER('WorkItems In Progress2', [WorkItemType] = "Bug"), "WorkItemId", [WorkItemId], "WorkItemType", [WorkItemType], "LeadTimeOrOpen", [TimeSinceCreated] ) )

5. Add to a number visual and then create an average of this
   

[aayaffe]

Thank you very much for the comprehensive reply! 🙏
It works very well. It is only missing the element of history.
What I did is add another column which is called DateClosedOrTodaySK which helps with visualizing it on the time line.
Do you think something like that can be implemented in your template?
The main issue will be connecting it to the WorkItems table because of relationship issues with the Date table. I am not sure how can that be solved.

[nbrown02]

So I won't be looking to add it into the template, just as it's the first time I've had a request like this and would mean a design change if it were to be a new chart, I have however just thought of how you can do it and solve your issue :)

1. Make a copy of WorkItems WIP Age (let's say this is called WorkItems Bug Age)

2. Change the 'source' to be just WorkItemType eq Bug and include CreatedDate and LeadTimeDays as columns to group by

3. Click close and apply

4. Create a relationship between Dates table and DateSK in WorkItems Bug Age (this solves our date issue)

5. Add a column DaysOpen = IF('WorkItems WIP Age'[LeadTimeDays] <> BLANK(), 'WorkItems WIP Age'[LeadTimeDays], 'WorkItems WIP Age'[DateValue]-'WorkItems WIP Age'[CreatedDate])

6. Create a measure Avg. Days Open = AVERAGE('WorkItems WIP Age'[DaysOpen])

7. Use 'Dates'[Date] for your x-axis and 'WorkItems Bug Age'[Avg. Days Open] for your y-axis:

[aayaffe]

This seems like a very good solution.
I have a little problem. When adding LeadTimeDays to the groupby field in the odata query, it return all null values. Do you have any Idea what can cause that?
Thank you.

[nbrown02]

It will be null until the day it is Done. As that table is showing you that value on a particular day. If I had an item in progress yesterday the LeadTimeDays would be 0 but if I moved it to done today then that column will populate (as the item is done). If that makes sense?

…

On Thu, Jun 22, 2023 at 3:52 PM aayaffe ***@***.***> wrote: This seems like a very good solution. I have a little problem. When adding LeadTimeDays to the groupby field in the odata query, it return all null values. Do you have any Idea what can cause that? Thank you. — Reply to this email directly, view it on GitHub <#102 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHACM2UBH7TTPZ4ZXIKVYLTXMRL3XANCNFSM6AAAAAAZM2DAOM> . You are receiving this because you commented.Message ID: ***@***.***>

[aayaffe]

I am not sure.
Does that mean that I should include in the basic query items that are in the done state?

[nbrown02]

I'd say yes :)

…

On Thu, Jun 22, 2023 at 4:02 PM aayaffe ***@***.***> wrote: I am not sure. Does that mean that I should include in the basic query items that are in the done state? — Reply to this email directly, view it on GitHub <#102 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHACM2UBXBZHVUZXBK6AR7TXMRNAJANCNFSM6AAAAAAZM2DAOM> . You are receiving this because you commented.Message ID: ***@***.***>