Forum Discussion
Calculate hours worked per day by team using TFS data
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)
Community SupportHi 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
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.
arsaveliHelper I
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)