Forum Discussion

aykim101's avatar
aykim101
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago
Solved

help with burndown chart

Thanks in advance for the help...

 

Trying to create a burndown line chart based on the sample data below

 

i cannot get the running total calculations to render the view.

the view would have the line chart start at 133 on 5/28 and trend downward to 58 by 10/31/17

 

DateCountBurndown
Start0133
05/29/171132
05/31/172130
06/01/171129
06/02/172127
06/07/171126
06/09/172124
06/12/172122
06/14/171121
06/15/171120
06/16/171119
06/23/171118
06/26/175113
06/27/172111
06/28/173108
06/30/176102
07/03/171101
07/04/171100
07/05/171387
07/06/17483
07/07/17677
07/08/17275
07/09/17273
07/10/17172
07/11/17171
07/12/17170
07/13/17268
07/24/17464
07/25/17163
07/31/17360
08/14/17159
10/31/17158
Need Help
  • samdthompson's avatar
    samdthompson
    9 years ago

    v-qiuyu-msft, I dont think that line looks quite right. Using the measure I outlined above, the result looks more like a burndown series. I have overlaid the two options for aykim101 to see:

     

    The line needs to continually drop from 133 by the increments of the count column to end at 58 on 31 October. Make sure that Date column is formatted as a date.

     

     

    Cheers,

     

    Sam

     

     

    // If this is a solution please mark as such

  • samdthompson's avatar
    samdthompson
    9 years ago

    Hi, as MFelix and I alluded to, there needs to be a context for the calculation to take place. The calculations I outlined were in lieu of having a definned 133 so start from. You will need a table with the starting point for each of the burndown eg:

     

    Project | Start point

    Project 1 | 133

    Project 2 | 150

    ...

    Project n | value

     

    Once you have that then and a relationship between the Project column and the Project column in your data table, you will be able to have the startpoint in your caluclation based on selection eg:

     

    SOLVE WITH MEASURE:

    Measure1:

    Project Start point measure = FIRSTNONBLANK('Project Table'[Start Point],1)

     

    Measure 2:

    [Project Start point measure]-CALCULATE( SUM('Table1'[Count]), FILTER(ALLSELECTED('Table1'[Date]),ISONORAFTER('Table1'[Date], MAX('Table1'[Date]),DESC)))

     

    Perhaps theres more to the origional post? Is the count column of the origional steps meant to sum to 133? If so then you could run a sum of the count column and use that as the starting point. =CALCULATE(SUM(Table1[COUNT]),ALL(Table1[DATE])) and use in lieu of the 133 in the calculation.

     

    Cheers,

     

    Sam

     

     

     

    // If this is a solution please mark as such

9 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    Hi aykim101,

     

    You can replace Start with "5/28/2017" and add a index column in Query Editor, then create a calculated column like below:

     

    Burn = var Pre = LOOKUPVALUE(Table1[Count],'Table1'[Index],'Table1'[Index]-1)
    return
    IF(Pre=2,'Table1'[Burndown]-2,'Table1'[Burndown])

     

     

    Best Regards,
    Qiuyun Yu

     

     

    • samdthompson's avatar
      samdthompson
      Icon for Memorable Member rankMemorable Member
      9 years ago

      v-qiuyu-msft, I dont think that line looks quite right. Using the measure I outlined above, the result looks more like a burndown series. I have overlaid the two options for aykim101 to see:

       

      The line needs to continually drop from 133 by the increments of the count column to end at 58 on 31 October. Make sure that Date column is formatted as a date.

       

       

      Cheers,

       

      Sam

       

       

      // If this is a solution please mark as such

      • aykim101's avatar
        aykim101
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        thank you all for the responses but i still cannot get it to work.... so frustrating.... :-(

         

        is there a way to sum the total of records 133 and then run the burn down?

         

        i tried both methods and i cannot get it to work

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    9 years ago
    Hi aykim101,

    Is the burndown a column on your data or do you want to calculate it in order to use it?

    If it is a column just add it to your visual and should give you what you want.

    MFelix
    • aykim101's avatar
      aykim101
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      the column values need to be calculated

       

      each line item has a date that would represent "1" so if there are 2 line items happening on the same day, the burn down chart would reduce by 2 after that day

       

      thanks

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        9 years ago

        HI aykim101,

         

        How do I know that the burndown starts at 133, how do you define the starting point of the burndown?

         

        MFelix

  • samdthompson's avatar
    samdthompson
    Icon for Memorable Member rankMemorable Member
    9 years ago

    Hi,

     

    You can do this either by calculated column or measure:

     

    first make sure your date column is a date and that the row currently defined as 'start' has a date in it (You cant have multiple data types in one column). For simplicity when testing I set it as 05/01/2017.

     

    SOLVE BY COLUMN:

    133-CALCULATE(sum(Table1[Count]),FILTER(Table1,Table1[Date]<=EARLIER(Table1[Date])))

     

    SOLVE WITH MEASURE:

    133-CALCULATE( SUM('Table1'[Count]), FILTER(ALLSELECTED('Table1'[Date]),ISONORAFTER('Table1'[Date], MAX('Table1'[Date]),DESC)))

     

    You can make the starting point dynamic with say a related table of different projects starting points but I am unsure how your model works.

     

    Cheers

     

    Sam

     

     

     

    // If this is a solution please mark as such