Days ADO Work Item is in each State

The need is to identify when the Azure DevOps (ADO) work item’s state changes and calculate the total number of days the work item is in each state. For example, determine the total number of days the work item is in the 1-New or 2-CSS Triage state and when the change occurred. This will be used in measures to calculate the average time in state per work item type.

I tired the approach from this article from the ADO team and it uses the Earlier function. This works fine for tables with low cardinality and is not performant for the WorkItemRevisions table due to the number of records in the dataset. Trying to implement the previous row pattern with variables to determine the previous state and calculate the difference in days with calculated columns. Encountering issues with the result.

For instance, work item ID 1966285 has the state changed multiple times in one day. The Date -Previous calculated column need to return the 7/1/2022 as the date. However, using the Index method on the RevisedDate field does not allow for the granularity needed for the index at the same day level.

The reason for multiple copies of the workitem per day with the same state is because a copy is made on each save action. The Revision field is incremented by one on each save of the work item.

Index created at the Date level:
DailyRev-Index =

Calculated Column:
Date-Previous =
VAR _CurrentRowIndex = ‘WORKITEM-REVS’[DailyRev-Index]
var _currentState = ‘WORKITEM-REVS’[State]
VAR _PreviousRowIndex = CALCULATE(
MAX( ‘WORKITEM-REVS’[DailyRev-Index] ),
‘WORKITEM-REVS’[DailyRev-Index] < _CurrentRowIndex
MAX( ‘WORKITEM-REVS’[RevisedDate] ),
FILTER( ‘WORKITEM-REVS’, ‘WORKITEM-REVS’[DailyRev-Index] = _PreviousRowIndex )

Calculation Needed:
The total number of days the work item is in each state
The date the state changes
The previous state

TiS-eDNA.pbix (409.3 KB)

Bumping this post for more visibility from our experts and users.

HI @EricParvin

While waiting for other members to jump in, I suggest using the forum search to discover if your query has been asked before by another member. Thanks!

Hi @EricParvin,

We noticed that your inquiry was left unsolved for quite some time now.
Looks like your inquiry was out of the experts and users’ bounds.

If you were sure you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, your membership also comes with relevant resources that may help you with your Power BI education, so we advise that you check these resources as well.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!