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

[HP]

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.

[tamerj1]

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]

Hi did you manage to find a work around?

[HP]

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