Why Display the ADO Sprint Status on a Power BI Report
Analytics Views (AV) provide the iteration start and end dates and the full path. It does not provide a field to show the past, future, or current iteration like on the task view in Azure DevOps (ADO). It is extremely helpful in the Power BI reports to display the sprint status to quickly filter the relevant work items. This is what it looks like on the Sprints view for the Task board:
On the backlog, you see the start and end dates for the sprint:
The Iterations ODATA entity includes the IsCurrent field, but it does not indicate whether iterations are past or future. The following DAX code can be used in reports that utilize Analytics Views and ODATA queries as the data model. The sprint status can be used in a slicer to filter the active work items.
Iteration and Sprint Meaning in ADO
In Azure DevOps, the terms iteration and sprint are often used interchangeably, but they have slightly different connotations:
- Iteration: This is a broader term that refers to any time-boxed period during which work is completed. Iteration paths can be used to group work into sprints, milestones, or other time-related periods.
- Sprint: This is a specific type of iteration used in Scrum methodology. Sprints are typically shorter, fixed-length periods (e.g., 2-4 weeks) during which a specific set of work is completed and reviewed.
In essence, all sprints are iterations, but not all iterations are sprints. Iterations can be used for various purposes beyond just Scrum sprints. This blog uses the term Sprint instead of Iteration.
Sprint Status Calculated Column Code:
The option I prefer for Power BI reporting is to use the following code to assign status on the iteration as a calculated column and use this value in a slicer, or table. This allows the report user to quickly identify the work items that are part of the current sprint using a text-based indicator. This approach simplifies the process and improves the clarity of the report.
The DAX code in the calculated column retrieves the work item’s sprint start and end dates and compares it to today’s date. It will assign the following status: Unassigned, Future, Past, or Current.
IterationStatus =
// Determines the status of the iteration based on the current date
// Rename the tables to match the AV tables in your report
VAR _currentEndDate = 'Features'[Iteration End Date]
VAR _currentStartDate = 'Features'[Iteration Start Date]
VAR _currentDate = TODAY()
var _returnValue =
IF(ISBLANK(_currentStartDate),
"Unassigned",
IF(_currentDate < _currentStartDate,
"Future",
IF(_currentDate > _currentEndDate,
"Past",
"Current"
)
)
)
return _returnValueThe IsCurrent Sprint Indicator:
The second option is assigning a 0 or 1 to the work item to signify the current iteration. The DAX can be used in a measure or calculated column within the Analytics View or ODATA query. The value of 0 or 1 is used in the DAX code to target the current iteration.
IsCurrent =
//Determines if the iteration is current based on the current date
var _currentEndDate = 'Features'[Iteration End Date]
var _currentStartDate = 'Features'[Iteration Start Date]
var _currentDate = TODAY()
var _isCurrent = IF(_currentDate >= _currentStartDate,
IF(_currentDate <= _currentEndDate, TRUE(),FALSE()), FALSE())
return _isCurrentChange the data type to Boolean.
The DAX code is written for an AV. If using this for the the Iterations ODATA, change to field names to EndDate and StartDate.
How to Display the ADO Sprint Status in the Report
The Analytics View creates a composite view of the analytics data and creates a single table data model. Check out this blog on what that means. Both of the code snippets will work as calculated columns.
The ODATA can be used in a star schema with the iterations as a dimension table. This is where the DAX code is placed if using a calculated column. If the ODATA query is returning the iteration information using the $expand clause to create a composite query, then use the DAX as a calculated column. The DAX code is best used in a measure or the table where the Iteration Start and End dates are located.