Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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:




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

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 =
    VALUES ( WorkItemRevision[WorkItemID] ),
    CALCULATE ( MAX ( WorkItemRevision[Effort] ) )
New Member

Hi did you manage to find a work around?

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

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors