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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NLondonLem
Regular Visitor

Agile Burnup Chart

Hi all

I would greatly appreciate some advice for how to create an agile burn up graph in Power BI

We have set up a link to Jira where our programme’s work is managed through the Jira Web API and we are pulling the information on Epics and Work (Stories, Tasks etc) into Power BI to visualise. One graph we are looking to create is the burn up to show the progress against scope for a defined period (programme increment – 12 week sprint cycle) or against a release, which are bits of metadata stored against the epics.


So far we have:

  • Extracted and set up the epic data into tables
  • Extracted and set up the work data (stories, tasks etc) into tables. The key relevant fields for this are the created date and the resolutiondate from Jira.
  • Created relationships between the work and epic tables, which work, so that we can filter by the key data about epics e.g. release, which will limit the associated work items
  • Created DAX measures for the cumulative count of created & resolution by date
  • Created a dynamic date table based on the earliest and latest dates available, linked to the work items in other tables

We would like to create a graph which looks like this:

https://robfrohman.com/blog-agilewhys/2016/11/28/how-to-create-a-release-burn-up-chart

https://spin.atomicobject.com/2016/03/31/burn-up-charts/

https://pm.stackexchange.com/questions/17207/optimistic-vs-pessimistic-burndown-lines-just-a-project...


The elements we’re looking to build in are:

  • A scope line that shows the cumulative count of created work, which plateaus as a flat line into the future when no more work is created but dates extend to the right on the x-axis
  • A work completed line which shows the cumulative count of resolved work and then a forecast into the future. Bonus would be a forecast with min/max envelopes based on criteria, like R forecasting does
  • The forecast could be a separate line behind the work completed line and dashed so that it looks like it continues out of the end of the work completed line in future to distinguish actual from forecast data
  • The forecast needs to extend until it meets at least the scope created line
  • X-axis needs to be the dates (presumably from date table) until the scope created line meets the forecast line i.e. enough into the future for the graph to work
  • Y-axis needs to be the number of items. In future we’ll consider making this story points but not all teams are using this yet so we need number of items as a rough proxy

We have considered / tried the following:

  • Power BI’s forecast functionality but we found it only supports one line at a time in a graph, which is a big limitation unfortunately and we prefer not to overlay two graphs
  • R forecasting but also found we couldn’t have more than one line and had strange errors e.g. not enough data when we had lots of data points (well more than the 7ish the documentation suggested it would need). We also prefer not to have to have each power bi desktop super user editor request permission from IT for locally installing the R packages required.

We think that we can do something using line graphs with DAX to provide the time series data with some kind of calculation based on the trends on historical data and a rough forward forecast but are a bit stuck. We’ve had a look online and tried applying solutions from similar queries but couldn’t find solutions that fit our needs.

Hopefully this will be re-usable thinking for other people delivering work in an agile way that want a similar graph. Advice and guidance would be much appreciated.
Thanks in advance!

3 REPLIES 3
v-yingjl
Community Support
Community Support

Hi @NLondonLem ,

As you mentioned, currently power bi forecast only supports in the line chart and the chart has to have only one line. Multiple-line charts don’t work, even if all but one line is filtered out.

 

As for using Dax to create a line about cumulative count in the chart, it is available, the formula could be like this:

Total = 
CALCULATE(
    SUM('Table'[Sales]),
    FILTER(
        ALL('Table'),
        'Table'[Date] <= MAX('Table'[Date])
    )
)

 

If you have any new ideas, you can create it here to help us improve power bi. It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Thank you Yingjie Li

Do you have any advice on re-creating a forecast line in DAX? Perhaps using some kind of moving average from the past to forecast into the future or other algorithm? I was thinking I could then have a binary switch where up to the line of real data it mirrors that and then it forecasts based on the historical data into the future with a dotted line. I can overlay the historical data line and stop it and the forecast line so it looks like they are one line to the user. The data is based on cumulatively when work is complete so there's no seasonal or other trends it is just an increasing velocity of work with a number of teams building up & sustaining momentum of development activity to close down scope work. Thank you

Hi @NLondonLem ,

If only have cumulative data without any confidence level or seasonal point, it could be difficult to create forecast value.

Perhaps you can define the forecast regulation and refer this issue to try to get the forecast value and create a chart to show it:

https://community.powerbi.com/t5/Desktop/Confidence-Level-95/td-p/275280

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.