Managing a BI or IT project and having a clear overview of the status of hours spent and available can be challenging. We often only look at the beginning budget and the total hours spent, with a negative amount meaning an exceeded budget. It often seems a mystery “when” and “why” a project was delayed; afterward, it’s hard to trace back what happened. The “why” of course, should be discussed with the team, but the “when” can be visualized – using a burndown chart in Power BI (or any other BI tool).
A burndown chart is a data visualization that can benefit project managers, BI/IT teams, and stakeholders. It shows hours spent, planned, and remaining hours (the line in the visual), giving you immediate insight into whether the project is on track, delayed, or at risk of exceeding the budget.
To create a burndown chart in Power BI, you do not need an additional visual from the AppStore. Creating a measure in DAX and using a line and column chart can do the trick! You need:
A burndown chart in Power BI can be created in 4 steps when the information required (budget hours, hours spent, hours planned (if available)) and date are available.
First, you want to know the total budget of hours available for a specific project. To get this information, you summarize the budget information you have available for a project.
In the example DAX, you see that also an ALL function is used. It ensures that the calculation is performed on all project IDs, regardless of any filtering or context in the report self.
Hours_Project_Budget=
CALCULATE ( SUM ( Project[Budget Hours] ),
ALL ( Project[Project ID] ))
Second, you need a measure that provides the cumulative hours spent. Cumulative means adding up:
| Week | Hours | Cumulative |
| 1 | 5 | 5 |
| 2 | 2 | 7 |
You need this measure by day (or week, month..) to understand how many hours are spent counting up. This way, you can, in the next step, subtract the hours from the budget hours.
In the DAX formula, you also see a filter on a date. This filter ensures that only hours are added up until the specific date (otherwise, all hours would be added up, regardless of the date).
Hours_Spent_Cumulative =
CALCULATE (
SUM ( 'Project Activity'[Hours Spent_and_Planned] ),
'Date'[Date] <= MAX ( 'Date'[Date] )
)
The final measure is a combination of steps 1 and 2. In the DAX, you see that I used variables for measures 1 and 2 and then used a return statement to subtract the results of steps 1 and 2.
This also means that you do not necessarily need to create separate measures in steps 1 and 2, but for learning purposes (and testing), creating these measures in the first place can be helpful.
This measure will subtract the cumulative hours spent from the budget depending on the pint of time (date). This is also the measure you will need to use as line in the line & column chart.
Burndown line =
var Hours_Project_Budget=
CALCULATE ( SUM ( Project[Budget Hours] ),
ALL ( Project[Project ID] ))
var Hours_Spent_Cumulative =
CALCULATE (
SUM ( 'Project Activity'[Hours Spent_and_Planned] ),
'Date'[Date] <= MAX ( 'Date'[Date] ))
return Hours_Project_Budget - Hours_Spent_Cumulative
To create the burndown chart, follow these steps:
The burndown chart in this article is based on the hours budget for a whole project. If the information is available, you could also create insights into project activities or, for example, employee performance. This, of course, all depend on the data available to you.
Feel free to contact me!
