Extracting Data from Azure DevOps

This process generates a row of numbers representing your team's throughput per day. We will use the "Closed Date" from Azure DevOps and aggregate it by day in Excel, ensuring days with no completed work are included as "0".

1, 0, 2, 0, 5

Log in to your Azure DevOps project and navigate to Boards > Queries, then click + New Query.

Set up the filters:

• Work Item Type | In Group | User Story (or other relevant types)
• State | = | Closed (or Done)
• Area Path | = | [Your Project]\[Your Team Name]
• Closed Date | >= | @Today - 365 (or your desired timeframe)

Crucial Step (Column Options):

1. Click Column Options in the top menu.
2. Add the column: Closed Date.
3. Click OK.
4. Click Run Query.
5. Click Export to CSV.

Create the PivotTable:

1. Open the CSV file.
2. Select your data, go to Insert > PivotTable, and click OK.
3. Drag Closed Date to the Rows area.
4. Drag ID to the Values area (ensure it's "Count of ID").

Group the Dates (Strip Timestamps):

1. Right-click on any date in the "Row Labels" and select Group....
2. In the pop-up, select only Days.
3. Set "Number of days" to 1.
4. Click OK.

CRITICAL: Force Zeros for Empty Cells

1. Right-click inside the PivotTable and choose PivotTable Options....
2. In the Layout & Format tab, check For empty cells show: and type 0 in the box.
3. Click OK.

CRITICAL: Fill in Missing Days

1. In the PivotTable fields panel, right-click the Date header (under Row Labels) and select Field Settings....
2. Go to the Layout & Print tab.
3. Check Show items with no data.
4. Click OK.

You should now have a continuous list of days, with "0" for days with no completed work.

1. Highlight the number column in your PivotTable (exclude the "Grand Total").
2. Copy the data (Ctrl+C or Cmd+C).
3. Open a new sheet and click on the first cell in the first column.
4. Paste the data.

This will give you a single column of daily throughput numbers needed for the forecast.