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 =
RANKX(‘WORKITEM-REVS’, ‘WORKITEM-REVS’[RevisedDate],ASC,Dense)
Calculated Column:
Date-Previous =
VAR _CurrentRowIndex = ‘WORKITEM-REVS’[DailyRev-Index]
var _currentState = ‘WORKITEM-REVS’[State]
VAR _PreviousRowIndex = CALCULATE(
MAX( ‘WORKITEM-REVS’[DailyRev-Index] ),
ALLEXCEPT ( ‘WORKITEM-REVS’, ‘WORKITEM-REVS’[WorkItemId] ),
‘WORKITEM-REVS’[DailyRev-Index] < _CurrentRowIndex
)
VAR _Result = CALCULATE(
MAX( ‘WORKITEM-REVS’[RevisedDate] ),
FILTER( ‘WORKITEM-REVS’, ‘WORKITEM-REVS’[DailyRev-Index] = _PreviousRowIndex )
)
RETURN
_Result
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)