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...
  • bolfri's avatar
    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