help with burndown chart

[aykim101]

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

Date	Count	Burndown
Start	0	133
05/29/17	1	132
05/31/17	2	130
06/01/17	1	129
06/02/17	2	127
06/07/17	1	126
06/09/17	2	124
06/12/17	2	122
06/14/17	1	121
06/15/17	1	120
06/16/17	1	119
06/23/17	1	118
06/26/17	5	113
06/27/17	2	111
06/28/17	3	108
06/30/17	6	102
07/03/17	1	101
07/04/17	1	100
07/05/17	13	87
07/06/17	4	83
07/07/17	6	77
07/08/17	2	75
07/09/17	2	73
07/10/17	1	72
07/11/17	1	71
07/12/17	1	70
07/13/17	2	68
07/24/17	4	64
07/25/17	1	63
07/31/17	3	60
08/14/17	1	59
10/31/17	1	58

[samdthompson · Accepted solution]

@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

[v-qiuyu-msft]

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 · Accepted solution]

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

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

[samdthompson · Accepted solution]

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

[aykim101]

probably needed to be more specific

small example

project | date

xyz | 5/23/17

abd | 5/30/17

das | 6/15/17

qop | 7/15/17

burndown total = 4

4 on the min date-1 (5/22)

5/23 3

5/30 2

6/15 1

7/15 0

[samdthompson]

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

[MFelix]

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]

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]

HI @aykim101,

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

MFelix