In this article, we will review how Power BI Report for Azure DevOps is built, what data is taken for reporting from Azure DevOps and how this data is calculated.
This report pack was built to display the time management of PPM Express Projects, linked to Azure DevOps projects. The pack provides various visualizations for tracking the reported time.
Reporting time aspects
The time for Work items in the report is defined by the native work item fields in Azure DevOps: Start Date and Due Date. Finish Date will be used instead of Due Date if Finish Date is filled in and its value is greater than Due Date value. If Due Date is greater than Start Date, then the fields swap their values.
All work item fields mentioned above are used in tracking time calculations: Original Estimate, Completed Work, Remaining Work, Start Date, and Due Date / Finish Date. These fields may not be provided for all work item types according to Azure DevOps layout process settings. For those work item types that require time tracking, it is required to add some of these five fields to the corresponding work item type layouts.
Example (work item type - Issue):
Data for report involved in tracking time calculations consists of two parts. The first part is taken from the Azure DevOps work item layout fields. The second part is taken from WeekendDays and UserCapacityPerDay(Hours) parameters and is provided to the reporting system among the other Power BI report parameters during the report configuration process.
Breaking down reported time by dates
Time in Azure DevOps is reported at the work item level. To make time tracking possible at the date level the reported time is broken down by dates in tracking time calculations.
There are two possible scenarios of time breaking:
1. All of the work item days are either working days or weekends.
2. Some of the work item days are working days, others are weekends.
In the second scenario, the reported time for weekends equals zero. For all other days (working days of the second scenario and any days of the first scenario) reported time is calculated in the following way:
A work item has no duration if its Start Date and Due Date / Finish Date aren’t filled in. The reported time in this case is accounted for the work item Created Date.
Capacity
A capacity is a number of hours a user works per day. The report parameter WeekendDays lists days of the week considered as weekends. For these days user capacity equals zero. For working days user capacity equals to UserCapacityPerDay(Hours) parameter value.
If a user’s workload is greater than one work item per day, then the user’s capacity for this day is shared between these work items proportionally to their Completed Work values.
Late Work
A project time span is defined by its work items durations. If for the past time periods there is no remaining work left and for each day of the future time periods reported time (Completed Work + Remaining Work) of each user doesn’t exceed the capacity limit then there is no late work on the project. Late work is a number of hours that exceed the project time span expectation.
The following picture illustrates the Late work concept.
Visualizations
The report visuals are 100% interactive. Any changes to the filters or selection of certain visualization areas result in a filter propagation to all interlinked visuals.
Most of the visualizations are equipped with a set of tools that provide additional functionality specific to a visual type. These tools can be reached at the top right corner of a visual:
These tools can be also reached out by the right click on the content of a visual:
