Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HP
New Member

How to analyze work item history from Azure DevOps in Power BI?

I'm currently trying to create extensive Power BI report for Azure DevOps work items. I got direct access with SQL to analytics tables and views. I have already checked the official documentation here, but most of the sample reports for historical data pretty much refers to WorkItemSnapshot table.

I also did some experiments with WorkItemSnapshot but that would force me to go with daily level reporting and I would be also interested of weekly or sprint level history. Also even with direct SQL call and some WHERE limitations it was really slow for longer periods (we got lot of stuff on db). I thought that since that table is anyway some sort of CROSSJOIN between dates and WorkItemRevision table, probably I could directly fetch the full table on use some DAX magic to achieve much more performant and flexible end result. Only thing is that I don't have enough of that magic and that is why I'm here.

Some measures I'm looking for:

  • Total efforts: daily, weekly, sprint, PI
  • Done efforts: daily, weekly, sprint, PI
  • Items moved out of sprint during sprint
  • Items moved to the sprint during sprint

To start with total efforts we would need sum of effort for last revision of each work item in current filter context, say single date. Probably we haven't modified work item on that date so we would need to fetch last revision before that date.

Here is simplified example of WorkItemRevision data for someone who is not familiar with Azure DevOps datamodel:

 

HP_1-1633497253728.png

 

Hopefully you could throw in at least some comments which direction I should look for.

3 REPLIES 3
tamerj1
Super User
Super User

Hi @Declano & @HP 
This would require a connected Date table with week, month, year etc.. columns to slice by as needed. The Total Efforts for eample can simply be

Total Efforts =
SUMX (
    VALUES ( WorkItemRevision[WorkItemID] ),
    CALCULATE ( MAX ( WorkItemRevision[Effort] ) )
)
Declano
New Member

Hi did you manage to find a work around?

Sorry. No. Probably compromized something but cannot remember any more.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.