Calculate hours worked per day by team using TFS data

[ElectricWizard]

Hello,

I've been asked to create a report using data from TFS that shows the hours worked per team per day. I guess it should use the same data that TFS uses to generate the Sprint burndown chart. 

Ideally, each team should be utilizing 48 hours (TFS effort) per day. What I can't figure out is how to use the data from TFS to calculate this. 

Is this even possible?

Does TFS store deltas for workitem effort?

Would this be found in the WorkItemSnapshot table?

If this data is available, how would I calculate it?

Sorry for the vague question, but I'm completely new to Power BI and am in over my head here. 

[v-yuta-msft]

Hi ElectricWizard

If you are going to connect to online service, you can refer to this documentation: https://docs.microsoft.com/en-us/vsts/report/powerbi/data-connector-connect?view=vsts. If you are connecting to on-premise version, please refer to the similar theard: https://community.powerbi.com/t5/Desktop/how-to-connect-on-premise-Team-foundation-server/td-p/57070.

Regards,

Jimmy Tao

[ElectricWizard]

Jimmy,

Thanks for the links, but I already have a connection to TFS working and can get both OData and Analytics from it.

My question was more about how I could create the specific report from the data available in TFS.

[arsaveli · Accepted solution]

WorkItemSnapshot table available via VSTS Analytics OData contains state of fields at the end of each day, so it could be used to track down trend of each day. The easiest way is to just chart aggregate of your field per team - the chart will show the trend (if you actually need a diff between each day, you'll need to write some DAX).

See https://docs.microsoft.com/en-us/vsts/report/extend-analytics/querying-for-trend-data?view=vsts

Please note that you should aggregate using odata syntax (per article) - not in powerbi. Aggregating in powerbi may result in too much data and slow query.

Note that the Analytics service is currently only available on VSTS (hosted product), not TFS (on-premise product)