Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
tcburge3
Helper I
Helper I

Creating a multi-line burnup linechart using multiple date columns

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:

  • Line 1: Rolling total of created bugs over time
  • Line 2: Rolling total of closed bugs over time

 

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 IDCreated DateClosed Date
110/7/202210/10/2022
210/7/202210/10/2022
310/9/202210/11/2022
410/10/202210/12/2022
510/11/2022 
610/11/202210/15/2022
710/13/202210/16/2022
810/11/202210/17/2022
910/11/202210/18/2022
1010/12/2022 
1110/13/2022 
1210/14/2022 
1310/15/2022 
1410/16/202210/19/2022
1510/16/202210/19/2022
1610/17/202210/19/2022
1710/18/202210/19/2022
1810/19/202210/19/2022
1910/23/2022 
2010/23/2022 
2110/24/2022 
2210/25/2022 
2310/25/2022 
2410/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.

tcburge3_0-1671518878706.png

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.

tcburge3_1-1671519028495.png

tcburge3_2-1671519101173.png

 

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.

1 ACCEPTED SOLUTION
bolfri
Solution Sage
Solution Sage

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:bolfri_0-1671559899317.png

 

PBIX file can be found here: https://we.tl/t-Br895KqNr6





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
bolfri
Solution Sage
Solution Sage

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:bolfri_0-1671559899317.png

 

PBIX file can be found here: https://we.tl/t-Br895KqNr6





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




bolfri
Solution Sage
Solution Sage

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.





Did I answer your question? Mark my post as a solution!

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Just sent the document and source data excel file

SanKing
Helper I
Helper I

Hello @tcburge3 ,
I would create measures that calculate created bugs and closed bugs:

CreatedBugs = CALCULATE(DISTINCTCOUNT(Sheet1[Bug ID]),Sheet1[Created Date])
ClosedBugs = CALCULATE(DISTINCTCOUNT(Sheet1[Bug ID]),Sheet1[Closed Date]).
Hope this will help.

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]. 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.