PowerBI and TFS_Warehouse burndown query

[chokedoke]

Hi All,

Before i ask a more general query i was wondering anyone could help with the below query

I am importing certain data from the TFS_warehouse to create some top level reports with easy filtering.

One of the graphs i need to create is a bar chart that shows the amount of WorkActual that has been completed each day between the start and end of a sprint and display even if nothing has been done

I am using the WorkItemHistoryView, DimDate and DimIteration tables

From these i edit on the start and end dates of an interation depending on which on i select from a slicer. The information is held in a startdate and finish date variable

The WorkItemHistoryView table also details all the workactual changes over the course of each day it has a change. I just dont know who to get it so that the graph shows the data i want to see. Any help would be really appreciated!

Thanks

[v-qiuyu-msft]

Hi @chokedoke,

Please share some sample data about those three tables and tell us the expected results corresponding to sample data. 

You can share sample data in Excel file, upload Excel to OneDrive or Dropbox then share link here. 

Best Regards,
Qiuyun Yu

[chokedoke]

Hi,

Included is an example table of the data i have to work with

What i need to try and do is calculate per person what their actual work per day is. The table shows an example of a single task for one person. I think that i need to find out the WorkItemHistoryBK with a revisionCount of 1 that is that created latest in the day and then use the WorkActual value from that row to sum up against all the others

Hopefully this will be enough to help me work out what calculation i need to use! I am very new to DAX

[v-qiuyu-msft · Accepted solution]

Hi @chokedoke,

You can try this measure, 

Measure= var latestPerDay= calculate(Max([System_CreatedDate]), filter(all('Table1'),[System_AssignedTo]=Max([System_AssignedTo]) && Datevalue(System_ChangedDate)=Max(Datevalue(System_ChangedDate)) && [RevisionCount]=1))
return
calculate(sum(WorkActual),filter(all('Table1'),[System_CreatedDate]=latestPerDay))

Best Regards,
Qiuyun Yu 

[chokedoke]

Hi,

That worked for me so thank you for the help!

[chokedoke]

I think i need to somehow convert the following into a calculation

get the workactual from workitemhistorybk with latest system changed date, then sum each day between the iteration.startdate and the iteration.finishdate

That should hopefully give me an increasing number over the course of the sprint