Forum Discussion

tcburge3's avatar
tcburge3
Helper I
3 years ago
Solved

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...
Need Help
  • bolfri's avatar
    bolfri
    3 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
tcburge3
Helper I
3 years ago

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