Creating a multi-line burnup linechart using multiple date columns

[tcburge3]

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

[bolfri · Accepted 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

[bolfri]

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.

[tcburge3]

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.

[bolfri]

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.

[tcburge3]

Just sent the document and source data excel file

[SanKing]

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.

[tcburge3]

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