Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Burndown Chart

Hi,

 

I am trying to create a burndown graph for a list of software activities (user stories), which have 3 date fields:

  1. Baseline Completion Date - Assigned to all activities when they are created and serves as initial baseline schedule.
  2. Forecast Completion Date - Live view on when an activity will complete.
  3. Actual Completion Date - Self explanatory. 

A simple example dataset is shown below along with required chart output. 

2.png

 

 

In my dataset, I have the activity name then have ungrouped the 3 data fields differentiating through 'attribute' field.

I have attempted to create the burndown graph as follows but the counts are not as expected; some records are missing and the counts jump.- My burndown measures are same for baseline/forecast/actual except for referring to different attributes.

 

Please could someone advise how I can resolve this issue and best achieve the chart?

 

1.png

1 ACCEPTED SOLUTION

Hi, @Anonymous 

If you do not want to show a spike , in my thought , we can compre this value before it  and then we can create a measure based on the [Actual Value] measure:

Measure = var _cur_date = MAX('Calendar'[Date])
var _preDate = MAXX(FILTER( ALLSELECTED('Table')  , 'Table'[Date]< _cur_date && 'Table'[Attribute] = "Actual Completion Date") ,[Date])
var _prevalue = CALCULATE( SUM('Table'[Baseline Burndown]) , 'Table'[Attribute]="Actual Completion Date" , 'Calendar'[Date] = _preDate)
return
IF([Actual Value]=BLANK(), BLANK() ,IF(ABS( [Actual Value]-_prevalue)>2 ,BLANK() , [Actual Value]))

 

Then we can put this measure on the viusal and we can hide this spike in it :

vyueyunzhmsft_0-1683011754856.png

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

View solution in original post

4 REPLIES 4
v-yueyunzh-msft
Community Support
Community Support

Hi , @Anonymous 

According to your description, you want to create a burndown graph.

Thanks for your sample file , in my thought you want to show three measures in a line chart visual . And for your sample data , it seems you want to show the Forcast value after the max of the Actual Value .

vyueyunzhmsft_1-1682996483739.png

 

This is my data apply to Desktop:

vyueyunzhmsft_2-1682996520328.png

First we can create a calendar Date Table as dimension:

Calendar = CALENDAR( FIRSTDATE('Table'[Date]) , LASTDATE('Table'[Date]))
 
And we need to create a relationship between two tables:
vyueyunzhmsft_3-1682996563143.png

Then we can create three measures like this:

BaseLine Value = CALCULATE( SUM('Table'[Baseline Burndown]) , 'Table'[Attribute] = "BaseLine Completion Date")
 
Actual Value = CALCULATE( SUM('Table'[Baseline Burndown]) , 'Table'[Attribute] = "Actual Completion Date")
 
Forcast Value =
var _actual_max_date = CALCULATE( MAXX( ALLSELECTED('Table') , [Date]) , 'Table'[Attribute] = "Actual Completion Date")
return
IF(MAX('Calendar'[Date]) = _actual_max_date , [Actual Value],
IF( MAX('Calendar'[Date])>_actual_max_date,
 CALCULATE( SUM('Table'[Baseline Burndown]) , 'Table'[Attribute] = "Forecast Completion Date" )))
 
Then we can put the measures on the visual and we can get it:
vyueyunzhmsft_4-1682996624542.png

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Anonymous
Not applicable

Hi @v-yueyunzh-msft ,

 

Thank you, this is very close to what i require and inspiring me to look at different ways but there is an issue that I wish to overcome which is well illustrated in your example where there is a spike caused by early closure of a record (1st June). I would like the chart to burn down incrementally such that on this date only a burndown of -1 is achived. This drove me (unsuccessfully) to using COUNT and disregarding the 'Baseline Burndown' field. How can I achieve this?

 

Thanks

Hi, @Anonymous 

If you do not want to show a spike , in my thought , we can compre this value before it  and then we can create a measure based on the [Actual Value] measure:

Measure = var _cur_date = MAX('Calendar'[Date])
var _preDate = MAXX(FILTER( ALLSELECTED('Table')  , 'Table'[Date]< _cur_date && 'Table'[Attribute] = "Actual Completion Date") ,[Date])
var _prevalue = CALCULATE( SUM('Table'[Baseline Burndown]) , 'Table'[Attribute]="Actual Completion Date" , 'Calendar'[Date] = _preDate)
return
IF([Actual Value]=BLANK(), BLANK() ,IF(ABS( [Actual Value]-_prevalue)>2 ,BLANK() , [Actual Value]))

 

Then we can put this measure on the viusal and we can hide this spike in it :

vyueyunzhmsft_0-1683011754856.png

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

Hi , @Anonymous 

Thanks for your quick response! Sure , in 2023/6/1 the value is "9" but in 2023/6/2 the value is "14" so it will be a spike due to the data will not deceit us.

For that , i do not fully understand the " I would like the chart to burn down incrementally such that on this date only a burndown of -1 is achived. This drove me (unsuccessfully) to using COUNT and disregarding the 'Baseline Burndown' field".
In my thought , you do not want to create a spike  in your chart , you want to the line will decrease slowly , just like each date will minus -1?

That is, for this test data, we will ignore the number that caused the peak and give it a suitable value again?

And I don't quite understand your intention to use the COUNT function, the test data you provided shows that you are drawing visuals according to the 'Baseline Burndown' field.

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors