ADO Sprint Capacity vs. Assignment Checker

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

Environment Variables

Connectors & Connections

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.

@{first(body('Parse_Sprint_Iteration')?['value'])?['id']}
@{first(body('Parse_Sprint_Iteration')?['value'])?['name']}

@if(equals(length(items('Loop_Through_Team_Members')?['activities']), 0), 0,
    mul(first(items('Loop_Through_Team_Members')?['activities'])?['capacityPerDay'], 5))

@json(concat('{"query": "SELECT ... FROM WorkItems WHERE [System.IterationPath] UNDER ''',
    variables('varIterationPath'), ''' AND [System.State] <> ''Removed'' ..."}'))

@greater(outputs('Compose_Member_Assigned_Count'),
    mul(outputs('Compose_Member_Capacity_Hours'), add(1.0, variables('varOverAllocationThreshold'))))

@{join(body('Build_Work_Item_IDs_List'), ',')}