如何衡量Azure DevOps中功能各状态耗时及解决Cycle Time组件局限

Great question! Let's tackle both parts of your problem step by step—measuring time spent in each state for Azure DevOps features, and working around the limitations of the built-in Cycle Time widget.

There are a few reliable ways to track how long features spend in each state, depending on your needs for customization and scalability:

1.1 Use Azure DevOps Analytics + Power BI (Most Flexible) #

Azure DevOps Analytics stores historical work item data, including state changes, which makes it perfect for calculating state-specific time. Here's how to approach it:

• Create an Analytics View (Project Settings > Analytics > Views) focused on Features, including fields like Work Item ID, Title, State, Revised Date, and State Category.
• Connect this view to Power BI. Use the WorkItemSnapshot table, which captures every state change event for each work item.
• Write DAX formulas to calculate time spent in each state. For example, to get the time between the current state entry and the next state change:

  TimeInState = 
  VAR PreviousChange = 
    CALCULATE(
      MAX(WorkItemSnapshot[Revised Date]),
      FILTER(
        ALLEXCEPT(WorkItemSnapshot, WorkItemSnapshot[Work Item ID]),
        WorkItemSnapshot[Revised Date] < EARLIER(WorkItemSnapshot[Revised Date])
      )
    )
  RETURN
    DATEDIFF(PreviousChange, WorkItemSnapshot[Revised Date], HOUR) // Or DAY, MINUTE depending on precision
  

• You can then aggregate this data to show average time per state, total time per feature across states, or any other custom visualization.

1.2 Custom Work Item Fields + Rules (No External Tools) #

If you prefer to keep everything within Azure DevOps, you can add custom fields and auto-populate them via rules:

• Add two custom date fields to your Feature work item type: State Entered Date and Time Spent in Current State (optional, calculated).
• Create a work item rule that triggers when the State field changes: set State Entered Date to the current date/time.
• To calculate total time spent in each state over the life of the feature, you’ll need to leverage the work item’s history. You can either:
  • Manually track time in a multi-line text field (not ideal for large datasets), or
  • Use the Azure DevOps REST API to pull historical state changes and compute time differences programmatically.

1.3 Azure DevOps REST API (For Automation/Custom Tools) #

If you need to build a custom solution or automate reporting, use the REST API to fetch work item history:

• Call the Work Items - Get Work Item endpoint with the $expand=all parameter to retrieve all state change events.
• Parse the revisions array to extract each state transition and its timestamp.
• For each state, calculate the time between entering and exiting it (summing multiple entries if the feature cycles back to the same state).

The built-in Cycle Time widget has two key limitations you mentioned—no state-level breakdown and a 180-day data cap. Here’s how to fix both:

2.1 Show State-Level Cycle Time #

The default Cycle Time widget only shows total cycle time, but you have better options:

• Use the "Lead Time and Cycle Time" Widget: Look for this widget when adding components to your dashboard. It includes a breakdown option that lets you visualize average time spent in each state (e.g., Active, Review, Done). Just configure it to target Features and select the "Breakdown by State" option.
• Embed a Power BI Report: Build a custom Power BI report (as outlined in Section 1.1) that shows state-specific cycle time, then embed it directly into your Azure DevOps dashboard. This gives you full control over visualization and data filtering.

2.2 Extend Beyond the 180-Day Limit #

The 180-day cap is hardcoded in the built-in widget, so you’ll need to bypass it with external tools:

• Power BI + Analytics Views: Analytics Views support custom time ranges (up to the full history of your project). When creating your view, set the date filter to include data beyond 180 days, then use Power BI to calculate cycle time and state metrics for the extended period.
• Export Data for Local Analysis: Export data from an Analytics View (or via REST API) to a CSV file, then use Excel or Google Sheets to compute cycle time and state time. Excel’s pivot tables and DATEDIF formulas make it easy to aggregate and analyze historical data beyond 180 days.

• Ensure the Analytics service is enabled for your project (Project Settings > Analytics Settings).
• When calculating time in state, account for cycle-back scenarios (e.g., a feature moving from "Review" back to "Active") by summing all time spent in each state across multiple transitions.
• For custom work item rules, test the state change triggers to ensure State Entered Date updates correctly every time the state changes.

内容的提问来源于stack exchange，提问作者Cemre Uludag