Forum

Forums
Public Forums - For...
Power Pivot
Backlog trend by ag...
 
Notifications
Clear all

Backlog trend by age bucket

 
Power Pivot
Last Post by Laurent Mahe 4 years ago
3 Posts
2 Users
0 Reactions
153 Views
RSS
 Laurent Mahe
(@laurentm)
Posts: 2
New Member
Topic starter
 
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
 
Posted : 28/04/2021 8:29 am
 Mynda Treacy
(@mynda)
Posts: 4761
Member Admin
 

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
 
Posted : 28/04/2021 8:10 pm
 Laurent Mahe
(@laurentm)
Posts: 2
New Member
Topic starter
 

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

 
Posted : 29/04/2021 9:58 am
Forum Jump:
  Previous Topic
Next Topic  
Share: