How can I add Lead time calculation inside Team Services and Visual Studio Team Services

Question

I have some questions about Visual Studio Team Services (previously Visual Studio Online).

Basically, I want to calculate the average lead time in team services Projects and to present measurement in Team services the same way as number of active bugs in a Query report. To be able to do that I have to find all work order items that have a finish/done date, sum all days between done_date - active_date for each work order divided by all work orders With done date. But it is difficult to do make customized calculations inside team services. So what possibilites do I have. I know there is a REST Api that can be used, or maybe I can use Excel in any way or maybe there is an extension possibility in Team services that I can use.

If I have to create a C# program that uses Rest API or may be I can use SQL Server 2016 in any way, Power Query or so, I also want to make the number available in Team services again or in Power BI to create a Dashboard where I can add this New measurement in team services again?

I hope I explained myself enough that you could understand my problem and I would be very happy if somebody has a good guidance or examples or link to articles that explain an approach to solve my issues,

Answer 1

By default, the Cumulative Flow Diagram (CFD) widget can show the basic information about lead time for work items. Refer to this link for details: Cumulative flow.

The CFD shows the count of items in each Kanban column for the past 30 weeks or less. From this chart you can gain an idea of the amount of work in progress and lead time. Work in progress counts unfinished requirements. Lead time indicates the amount of time it takes to complete a requirement once work has started.

If this does not fit the feature you want, you can use Excel to get the data and general the report you want.

Requirement to connect to VSTS from Excel:

1. Office Excel 2007 or later
2. Visual Studio 2010 or later or Team Foundation Server Standalone Office Integration 2015 (free),

Reference for connect to VSTS: https://msdn.microsoft.com/en-us/library/vs/alm/work/office/bulk-add-modify-work-items-excel

Then you can do it by following the steps below:

1. Create a work item query in VSTS which will get all the work items you want. And in the "Column options" setting, select "Activated Date" and "Closed Date".(This depends on what date you want)

2. From Excel, select "Team->New List" and select the VSTS account and project. In "Query list" dropdown list, select the query you created in last step.

Now you can generate the lead time report base on the data you get.