How to create cumulative charts (CFD, Burn Up/Down charts)

Cumulative reports are not so easy to create. You should be patient and learn how to copy and paste some formulas. I'll try to explain how you can create a Cumulative Flow digram, Burn Up chart, and Burn Down chart.

Let's say, we have an app with Releases, User Stories, and Bugs, here is the schema:

In this video I show how to create CFD and Burn Down charts:

Cumulative Flow Diagram (CFD)

Cumulative Flow Diagram is handy to understand how Cycle Time changes and get the feel of the whole process. We are going to build a CFD for Bugs and User Stories in our app:

Step 1. Select Report type

Create a new Report and select "Cumulative, Burn Up & Down charts". Select required entities (User Stories and Bugs in our case):

Step 2. Set X-axis using Timeline function

Timeline is a special date function used for grouping data by date period, defined by start and end. Here is the detailed description.

TIMELINE(
  start date expression, 
  end date expression, 
  stop on current date (true or false, optional), 
  timeline start field (optional), 
  timeline end field (optional))

Optional stop on the current date can be true or false to indicate if a timeline should be stopped at the current date.
Optional timeline start and end fields are used to get min and max dates for date scale.
Date expression: Date calculation created using date functions, data fields, or constants.

For our needs we should use this code:

AUTO(
	TIMELINE(
		[Modification Date],
		[Modification Valid To],
		TRUE)
)

Here is how you do it:

Step 3. Set Y-axis as count of entities

Click + sign in Y axis and set the formula like this:

COUNT([Id])

It will calculate the number of entities in some states for some date.

Step 4. Set Chart Type and States as colors

Now you should set State field as a color to calculate state transitions and set a chart type. You can also change the time group to Weeks or Days for X-axis:

Burn Down Chart

Let's create a Burn Down Chart for Release 1.0. It will be a chart that shows the remaining effort of Bugs and User Stories in a release and shows Ideal and Forecast lines:

Step 1. Filter data by Release 1.0

We want to see Stories and Bugs from Release 1.0 only, so we will create a Filter, like this:

Step 2. Set X-axis Timeline function

Now we should set a TIMELINE function like this (note that two last values cut chart by release dates):

AUTO(
	TIMELINE(
		[Modification Date],
		[Modification Valid To],
		TRUE,
		MIN([Release Dates Start]),
		MAX([Release Dates End])
	)
)

Step 3. Set Y-axis and chart type

In a Burn Down we calculate the remaining Effort, so in Y-axis we will set a basic formula

Sum(Effort To Do)

And we'll set Line as a chart type:

Step 4. Add an Ideal line

The ideal line should just move from the first to the last day of the release and from the top effort to zero. It can be created as an Annotation, use this IDEAL_LINE function:

IDEAL_LINE(
	FIRST([SUM(Effort To Do)]),
	0
)

Here is how you add a new Annotation line:

Step 5. Add a forecast line

The forecast line shows basic prediction about release completion date based on team velocity. We'll use FORECAST_LINE function:

FORECAST_LINE(
	[Sum(Effort To Do)],
	0
)

Burn Up Chart (by Effort)

Burn Up Chart is somewhat more informative than Burn Down, since it shows how scope changes over time. For example, in this chart, we see that the release scope was increased twice (blue line). Red line shows completed effort, so you can create a forecast line that will show when the whole scope will be completed:

Step 1. Filter data by Release 1.0

We want to see Stories and Bugs from Release 1.0 only, so we should create a Filter, like this:

Step 2. Set X-axis Timeline function

Now we will set a TIMELINE function like this (note that two last values cut chart by release dates):

AUTO(
	TIMELINE(
		[Modification Date],
		[Modification Valid To],
		TRUE,
		MIN([Release Dates Start]),
		MAX([Release Dates End])
	)
)

Click on + in X axis and set a calculation like this:

Step 3. Set Total and Completed Efforts on Y-axis

Y-axis will show two things:

Total Effort
Effort Completed

We'll calculate Effort Completed like this:

SUM([Effort] - [Effort To Do])

Let's set both efforts in the chart:

Step 4. Add Ideal Line and Forecast Line

The ideal line goes from 0 to the last date of the release, basically. Here is the formula:

IDEAL_LINE(
	FIRST([Effort Completed]),
	LAST([Total Effort])
)

Forecast Line shows when the scope of the Release can be completed:

FORECAST_LINE(
	[Effort Completed],
	LAST([Total Effort])
)

Click on Total Effort settings and add these two annotations:

Historical Data Structure

We always used [Modification Date] and [Modification Valid To] fields in cumulative charts, but what the structure of the historical data? Let's check this raw data to understand that.

This data shows changes for a single entity ("Full Text Search" Feature) from 15 to 19 April:

For example, the first row shows that State was set to Open on 15 April 2021 and this information was valid till 15 April 2021. It also means that "Full Text Search" Feature was changed again on 16 April and something changed here.

Basically, this is a list of events that indicate what was changed, when and for how long these changes are valid.