Guide
Historical/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 diagram, Burn Up chart, and Burn Down chart.
A cumulative report is an example of a report using historical data. See Current vs Historical data in Reports.
You can't build a historical context report using many-to-many relations
Let's say, we have a Space with Releases, User Stories, and Bugs. In this video I show how to create CFD and Burn Down charts:
https://youtu.be/U0nh2j8_mmM
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 Space:
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 have to create a Filter or (that's better!) use Context Views
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
)
Note: if instead of Effort, you use other parameters to estimate your Stories (or Tasks, Issues, or Tickets) - please, use it instead of the Effort field.
If you want to build a Burndown chart based on the number of Done items, this guide is for you BurnDown Chart based on "Done" Cards Counts
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). The red line shows the 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 the X-axis Timeline function
Now we will set a TIMELINE function like this (note that the two last values cut the 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 the 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 tend to use [Modification Date] and [Modification Valid To] fields in cumulative charts, but what is 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.
You can learn more about Current vs Historical data in Reports.
Filters
You can also apply filters on creation. See here.
Note that filters are by current values only, so set filters wisely to not filter out important data. For example, if you create a report for a Release and want to know what entities were moved out to another release, do not filter by Release then, since these entities will not be included.
FAQ
I don't see my formula field and can't use it in report
Formulas, that are based on “today” syntax are automatically excluded from fields set for the historical report
I can't find "Created by …" filter
That was excluded on purpose, as we don't support dynamic results for historical reports. You can use Context Views with User on the top level instead, Please, let us know if you need that functionality.
Can I use Duration calculation in filters?
Even though Duration appears in the "Data Fields" section, it's not a field, but a complex formula. Similar to Timeline, Count of Records, etc.
We added those formulas for simplicity, to allow drag and drop. As only a few people are able to write the Duration formula themselves 😅.
You can't filter by a calculation using field filters.
But you can filter by those aggregated values in the chart data section.
How does the Date Range field work during the initial setup?
The report will show entities that were not created but modified within the last 90 days. For example, if an entity was created one year ago and modified within the last 90 days, it will appear in the report.
Please, note that on Standard plan history is limited for 90 days.
Use Pro version for the unlimited history.