Excel Forum

Backlog trend by age bucket

Laurent Mahe

New Member

Members

Forum Posts: 2

Member Since:
February 15, 2017

Offline

April 27, 2021 - 10:29 pm

I've recently received a request to display a bug backlog (so # of open bugs) over time, broken down by age bucket, i.e. how many open bugs that were less than 1 week old did I have over time

I can do the backlog over time, see attached sample power bi file.

Where I'm really struggling is to compute the bug age over time so I can have my backlog trend by bugs <1 week old, <2 weeks old, etc

Thanks

Mynda Treacy

Admin

Forum Posts: 4773

Member Since:
July 16, 2010

Offline

April 28, 2021 - 10:10 am

Hi Laurent,

Welcome to our forum!

You can add a column to the Data table and use this formula to calculate the age of each bug:

Age = IF(ISBLANK(Data[Closed Date]),TODAY(),Data[Closed Date]) - Data[Created]

Set the data type as Whole Number. You can then use this new field in the chart horizontal axis.

If you want the 'age' values grouped you can add another column that classifies the age into bands using a SWITCH formula e.g.:

Age Band = SWITCH(TRUE(),

Data[Age]<8,"<1 week old",

Data[Age]<15,"2 weeks old",

Data[Age]<22,"3 weeks old",

Data[Age]<29,"4 weeks old",

Data[Age]>28,">1 month old")

With this column you need to set the sort order of the bands using a dimension table and use the age bands field from this dimension table in your visuals.

You'll also need to add a column to classify the bugs into status: open or closed (I've added this column in Power Pivot, but you could add this column in Power Query, which might give slightly better performance results if your dataset is large)*. This will allow you to filter out the closed bugs from your visuals.

*Note: I see you've added measures to ignore closed bugs, however I think it's more intuitive to add a column that classifies the bugs into open/closed. It's up to you at the end of the day, but that's how I'd do it, as it's then clearer what is being included/excluded from your visuals.

See file attached.

Mynda

Laurent Mahe

New Member

Members

Forum Posts: 2

Member Since:
February 15, 2017

Offline

April 28, 2021 - 11:58 pm

Hi Mynda,

Thanks a lot for your reply, although I must have not expressed myself correctly.

I can calculate the bug age and then categorise it in bands, but what I'm looking for is a way to have this over time.

What you provided me with (and thanks) is just a categorisation of bugs by age based on today.

I can't "go back in time" to see how it looked like in the past.

In the same way that I have the number of open bugs over time, I want the same, but instead of having one line showing the total, I want multiple lines, one per each category.

A bug would progress through different categories over its life.

Each line should represent the number of open bugs by age at that time (e.g. not "now")

Hope this clarifies what I'm after