ADO Sprint Capacity vs. Assignment Checker
Runs at sprint start and mid-sprint. Compares each team member's capacity hours against their total assigned story points and remaining work. When anyone is over-allocated by more than 20%, posts a Teams warning to the scrum master and sends an Outlook summary with rebalancing suggestions.
Provided as-is, without warranty of any kind. Review and test each pattern in a non-production environment before deploying it to live automations. See our Terms.
Overview
Runs twice per week (Mon/Wed 9am EST) against an Azure DevOps project to pull the current iteration, team capacities, and all active in-sprint work items via a real WIQL query. Computes per-person assignment count vs. capacity hours and flags anyone over the configurable threshold (default 20% above capacity). When any team member is over-allocated, sends a formatted HTML email and posts a Teams alert to the scrum channel.
Use Case
Prevents scrum masters from discovering mid-sprint that a teammate is buried. Surfaces capacity vs. assignment mismatches early enough to rebalance work before velocity suffers. Useful for any ADO-tracked team running iterations with the Capacity planning feature enabled.
Flow Architecture
Recurrence - Weekly Sprint Check
RecurrenceWeekly, Mon+Wed 9:00 EST.
Initialize varAdoOrganization
InitializeVariableReads value from flowlibs_AdoOrganization environment variable.
Initialize varAdoProject
InitializeVariableReads value from flowlibs_AdoProject environment variable.
Initialize varIterationPath
InitializeVariableReads value from flowlibs_AdoSprintIterationPath environment variable.
Initialize varTeamsGroupId
InitializeVariableReads value from flowlibs_TeamsGroupId environment variable.
Initialize varTeamsChannelId
InitializeVariableReads value from flowlibs_TeamsChannelId environment variable.
Initialize varNotificationEmail
InitializeVariableReads value from flowlibs_NotificationEmail environment variable.
Initialize varOverAllocationThreshold
InitializeVariable (float)Default 0.2 (20%).
Initialize varOverAllocatedMembers
InitializeVariable (array)Accumulates flagged member names.
Initialize varAlertHtml
InitializeVariable (string)Accumulates HTML table rows.
Environment Variables
| Schema name | Type | Default | Description |
|---|---|---|---|
| flowlibs_AdoOrganization | String | <configure> | Azure DevOps organization name (the segment after dev.azure.com/). |
| flowlibs_AdoProject | String | FlowLibs | Azure DevOps project name that contains the sprint/iteration. |
| flowlibs_AdoSprintIterationPath | String | <configure> | Iteration path used in WIQL UNDER clause (e.g. Project\Sprint 12). |
| flowlibs_TeamsGroupId | String | <configure> | GUID of the Microsoft Teams group/team that receives the over-allocation alert. |
| flowlibs_TeamsChannelId | String | <configure> | ID of the channel within the Teams group where the alert is posted. |
| flowlibs_NotificationEmail | String | alerts@yourcompany.com | Email recipient for capacity alert summaries. |
Connectors & Connections
| Connector | API name | Actions used |
|---|---|---|
| Azure DevOps | shared_visualstudioteamservices | HttpRequest (Get Current Sprint Iteration (GET iterations?$timeframe=current)) HttpRequest (Get Team Capacity for Sprint (GET iterations/{id}/capacities)) HttpRequest (Query Assigned Work Items in Sprint (POST WIQL)) HttpRequest (Get Work Item Details Batch (GET workitems?ids=...)) |
| Office 365 Outlook | shared_office365 | SendEmailV2 (Send Capacity Alert Email (Importance: High)) |
| Microsoft Teams | shared_teams | PostMessageToConversation (Post Over Allocation Alert to Teams (Flow bot to channel)) |
Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.
Customization Guide
Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.
- Update environment variables
- Set flowlibs_AdoOrganization, flowlibs_AdoProject, and flowlibs_AdoSprintIterationPath to match the target project. Set Teams group/channel IDs and the notification email.
- Tune the over-allocation threshold
- Edit Initialize_varOverAllocationThreshold. Default 0.2 (20%). Raise to 0.3 for teams with looser estimation; lower to 0.1 for tight capacity planning.
- Adjust the recurrence
- Change weekDays on the Recurrence trigger to add or remove days. Current schedule is Mon+Wed at 9am EST.
- Match the sprint length
- The capacity hours formula uses capacityPerDay x 5 (1-week sprint). For 2-week sprints change to x 10 inside Compose_Member_Capacity_Hours.
- Authorize connections
- Open the flow in the designer, click each connection reference (Azure DevOps, Outlook, Teams), and authenticate with appropriate credentials for the target tenant.
Key Expressions
The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.
EXPR.01Current iteration id and name
Pulls the active iteration's id and display name from the parsed iteration response.
EXPR.02Capacity hours (1-week sprint)
Converts the member's first-activity capacityPerDay into total sprint hours; guards against empty activities arrays.
EXPR.03WIQL query body
Builds the POST body for the WIQL query, injecting the iteration path variable into the UNDER clause.
EXPR.04Over-allocation check
True when the member's assigned count exceeds capacity hours scaled by (1 + threshold).
EXPR.05Work item ID batch
Flattens the Select output into the comma-separated id list used by the work item details GET.
Comments
Sign in to join the conversation.
Sign inNo comments yet. Be the first to share your experience with this flow.