Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello, I am using powerbi for tracking bugs across a development program. I am hoping to build a burnup chart (a single chart with [date] in the x-axis) containing 2 lines:
The following Data is available to me. The logic goes, if an item has a [Closed Date] then it was closed on that date, otherwise, it is still open.
Bug ID | Created Date | Closed Date |
1 | 10/7/2022 | 10/10/2022 |
2 | 10/7/2022 | 10/10/2022 |
3 | 10/9/2022 | 10/11/2022 |
4 | 10/10/2022 | 10/12/2022 |
5 | 10/11/2022 | |
6 | 10/11/2022 | 10/15/2022 |
7 | 10/13/2022 | 10/16/2022 |
8 | 10/11/2022 | 10/17/2022 |
9 | 10/11/2022 | 10/18/2022 |
10 | 10/12/2022 | |
11 | 10/13/2022 | |
12 | 10/14/2022 | |
13 | 10/15/2022 | |
14 | 10/16/2022 | 10/19/2022 |
15 | 10/16/2022 | 10/19/2022 |
16 | 10/17/2022 | 10/19/2022 |
17 | 10/18/2022 | 10/19/2022 |
18 | 10/19/2022 | 10/19/2022 |
19 | 10/23/2022 | |
20 | 10/23/2022 | |
21 | 10/24/2022 | |
22 | 10/25/2022 | |
23 | 10/25/2022 | |
24 | 10/25/2022 |
The following is 2 line graphs overlayed eachother. I am hoping to create a similar visual but put both of these lines on the same line chart.
NOTE: The numbers on this example chart are not reflective of the numbers in the data given above, just purely for visual aid.
Blue line = Sum of created bugs over time
Green line = Sum of Closed bugs over time
The following is my relationship model. I have the date table created, but am having trouble counting the running total of Bugs using the relationship.
Any help is very appreciated! Please let me know if you can help. If I need to change any of my steps so far I can definitely do that too.
*****My next challenge:
I am also hoping to create a seperate line that shows what the delta is between these 2 lines for each day. So for example, if a Bug ID row where there is no closed date but there is a created date, then that row would count as 1 on the date it was created (showing as an open item). Really hoping to add this to the multi-line burnup chart, but not sure if this is feasible within powerbi.
Thank you so much for you time and consideration and if I can provide anymore information please let me know.
Solved! Go to Solution.
Date table:
hasData =
var min_date = CALCULATE(MIN(FIRSTDATE('Append Bugs'[Created Date]);FIRSTDATE('Append Bugs'[Completed Date]));ALL('Append Bugs'))
var max_date = CALCULATE(MAX(LASTDATE('Append Bugs'[Created Date]);LASTDATE('Append Bugs'[Completed Date]));ALL('Append Bugs'))
return IF(AND([Date]>=min_date;[Date]<=max_date);"Yes";"No")
I put a check if date is in between min and max date in date, so visuals looks better. I've put this filter in a page filter panel.
Basic Measures
Definition of a bug:
Number of bugs = COUNTROWS('Append Bugs')
Definition of a new bug:
New bugs = CALCULATE([Number of bugs];USERELATIONSHIP('Date'[Date];'Append Bugs'[Created Date]))
Definition of a closed bug:
Closed bugs = CALCULATE([Number of bugs];USERELATIONSHIP('Date'[Date];'Append Bugs'[Completed Date]))
RT Measures
Definition of a bug RT:
Number of bugs RT =
var ref_date = LASTDATE('Date'[Date])
return CALCULATE([Number of bugs];FILTER(ALLSELECTED('Date');[Date]<=ref_date))
Definition of a new bug RT:
New bugs RT = CALCULATE([Number of bugs RT];USERELATIONSHIP('Date'[Date];'Append Bugs'[Created Date]))
Definition of a closed bug RT:
Closed bugs RT = CALCULATE([Number of bugs RT];USERELATIONSHIP('Date'[Date];'Append Bugs'[Completed Date]))
Bonus - Opened bug each days:
Open bugs = [New bugs RT] - [Closed bugs RT]
Results:
PBIX file can be found here: https://we.tl/t-Br895KqNr6
Proud to be a Super User!
Date table:
hasData =
var min_date = CALCULATE(MIN(FIRSTDATE('Append Bugs'[Created Date]);FIRSTDATE('Append Bugs'[Completed Date]));ALL('Append Bugs'))
var max_date = CALCULATE(MAX(LASTDATE('Append Bugs'[Created Date]);LASTDATE('Append Bugs'[Completed Date]));ALL('Append Bugs'))
return IF(AND([Date]>=min_date;[Date]<=max_date);"Yes";"No")
I put a check if date is in between min and max date in date, so visuals looks better. I've put this filter in a page filter panel.
Basic Measures
Definition of a bug:
Number of bugs = COUNTROWS('Append Bugs')
Definition of a new bug:
New bugs = CALCULATE([Number of bugs];USERELATIONSHIP('Date'[Date];'Append Bugs'[Created Date]))
Definition of a closed bug:
Closed bugs = CALCULATE([Number of bugs];USERELATIONSHIP('Date'[Date];'Append Bugs'[Completed Date]))
RT Measures
Definition of a bug RT:
Number of bugs RT =
var ref_date = LASTDATE('Date'[Date])
return CALCULATE([Number of bugs];FILTER(ALLSELECTED('Date');[Date]<=ref_date))
Definition of a new bug RT:
New bugs RT = CALCULATE([Number of bugs RT];USERELATIONSHIP('Date'[Date];'Append Bugs'[Created Date]))
Definition of a closed bug RT:
Closed bugs RT = CALCULATE([Number of bugs RT];USERELATIONSHIP('Date'[Date];'Append Bugs'[Completed Date]))
Bonus - Opened bug each days:
Open bugs = [New bugs RT] - [Closed bugs RT]
Results:
PBIX file can be found here: https://we.tl/t-Br895KqNr6
Proud to be a Super User!
Can you share a pbix file with dummy data? 🙂
I think userelationship will help in this case. I had similar problem. I can show you solution on your sample data.
Proud to be a Super User!
Hey @bolfri yes I would be happy to do that. Im not sure how though. Would you recommend me sending you an email or is there somewhere on here I can add a pbix file? Your time is very appreciated.
I've send you my e-mail adress. Let me know when you will send a file.
I will post my answer/solution here so everyone can use it in the future.
Proud to be a Super User!
Just sent the document and source data excel file
Hello @tcburge3 ,
I would create measures that calculate created bugs and closed bugs:
Hi, thanks for reaching out on this. Unfortunately this won't work as a solution as I can't use both [Created Date] column and [Closed Date] column as the x-axis on the same line Graph. I believe the solution would entail using 'Date'[Date] column in the x axis of the line chart, and a running total measure that encorporates the relationship between 'Date'[Date] with both 'Append Bugs'[Created Date] and 'Append Bugs'[Closed Date].
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
82 | |
67 | |
61 | |
46 | |
45 |