Search code examples
azure-devopspowerbidax

Can I use Power BI to report on how long Azure DevOps work items spend in each state?

I am using Power BI to query work item data from Azure DevOps' Analytics Views. One of the things I'd like to do is produce a report showing how long work items are spending in different states e.g. New, Development, Testing etc.

To do this, I created an analytics view that pulls work item history from the API. The data model looks like this:

Work Item Id State Date IsCurrent
1 New 14/08/2024 0
1 New 15/08/2024 0
1 Development 16/08/2024 1
2 New 15/08/2024 0
2 Development 16/08/2024 0
2 Done 20/08/2024 1
3 New 10/08/2024 0
3 Done 20/08/2024 1
4 New 10/08/2024 0
4 Development 11/08/2024 0
4 New 12/08/2024 1
5 New 10/08/2024 0
5 Development 10/08/2024 0
5 Done 10/08/2024 1

As you can see, there is an entry for each change that was made to the item, but the change made wasn't necessarily to change the state.

What I want is to group the data by Work Item Id and State, and produce a date difference field that shows how long each item spent in each given state. What I'm struggling with is that the time spent in a state e.g. Development is calculated by the difference between the date it first entered the development state, and the date it moved into the next state e.g. Done.

So, for the data above, what I'd be looking to get is something like this:

Work Item Id State Time (Days)
1 New 3
1 Development
2 New 1
2 Development 4
2 Done
3 New 10
3 Done
4 New 0
4 Development 0
4 Done

I can think of how I'd do this in a programming language I'm familiar with, but I'm new to Power BI. Is this in any way possible?

So far, I've tried using the RANKX function to assign each item a rank based on its Date and Work Item Id:

Rank = RANKX(
    FILTER(
        'DevOps History'
        , 'DevOps History'[Work Item Id] = EARLIER('DevOps History'[Work Item Id])
    )
    , 'DevOps History'[Date].[Date]
    , 
    , ASC
)

This ranks all the history entries for each Work Item Id by their date, but I can't get it to also group by State and I don't know how to then calculate the date difference between each.

Solution

  • First I summarized the DevOps History table to get a single date for each Work Item Id, State, IsCurrent, and Date.

    From my experience a work item rarely goes straight from New->Dev->Done. It often goes from New->Dev->New->Dev->Done, so I added logic to get the first time a work item enters New and the last time it enters Dev/Done. Logic has also been added to retrieve the isCurrent status of a duplicate state.

    Summarized History = SUMMARIZE('DevOps History', 'DevOps History'[Work Item Id], 'DevOps History'[State], 

    "IsCurrent", //If a state is visited multiple times, this gets whether it is currently active or not
    MAX('DevOps History'[IsCurrent]), 
    "Date", //If a work item goes from New->Dev->New->Dev->Done this step step gets the firt time it entered new and the latest time it entered Dev/Done

    IF(MAXX('DevOps History', [State]) = "New", MINX('DevOps History', [Date]), //First time work item entered New
        MAXX('DevOps History', [Date])) //Last time work item entered not New state

)

    Then I created a States table for each State and the Order they appear in the board.

    Note: This can be automated using this API

    State Order
    New 1
    Development 2
    QA 3
    Done 4

    Connect States[State] to Summarized History[State] by a 1 to Many relationship.

    On the Summarized History table, created a Calculated Column:

    State Order = RELATED(States[Order])

    Then I added a calculated column to this table to find the work item age:

    Time (Days) (Updated) = 
//Get this rows data
VAR thisStateOrder = [State Order]
VAR thisDate = [Date]
VAR thisCurrent = [IsCurrent]
VAR thisID = [Work Item Id]

//find next state the workitem goes into
VAR workItemStates = FILTER('Summarized History', 'Summarized History'[Work Item Id] = thisID && [State Order] > thisStateOrder) //Get all states that are not current state or previous state
VAR workItemNextState = MINX(workItemStates, [State Order]) //Get next state in order
VAR workItemNextStateTable =FILTER(workItemStates, [State Order] = workItemNextState) //filters table to just the next state in order

//find the date of the next state
VAR nextStateDate = MAXX(workItemNextStateTable, [Date])

//find the age of current state
VAR TimeDays = IF(thisCurrent <> 1,
    DATEDIFF(thisDate, nextStateDate, DAY),
    BLANK()
)

RETURN TimeDays

    Input DevOps History:

    Work Item Id State Date IsCurrent
    1 New 07/14/2023 0
    1 New 07/15/2023 0
    1 Development 07/16/2023 1
    2 New 07/15/2023 0
    2 Development 07/16/2023 0
    2 Done 07/20/2023 1
    3 New 07/14/2023 0
    3 Development 07/17/2023 1
    4 New 07/16/2023 0
    4 Development 07/18/2023 0
    4 Done 07/21/2023 1
    5 New 07/17/2023 0
    5 Development 07/19/2023 0
    5 Done 07/22/2023 1
    6 New 07/17/2023 0
    6 Development 07/19/2023 0
    6 Done 08/15/2023 1
    6 New 07/23/2023 0
    6 Development 08/01/2023 0
    7 New 07/17/2023 0
    7 Done 08/01/2023 1
    8 New 07/17/2023 0
    8 Done 07/19/2023 0
    8 Development 07/23/2023 0
    8 Done 08/11/2023 1

    Output Summarized History:

    Work Item Id State Date IsCurrent State Order Time (Days) (Updated)
    1 New 07/14/2023 0 1 2
    1 Development 07/16/2023 1 2
    2 New 07/15/2023 0 1 1
    2 Development 07/16/2023 0 2 4
    2 Done 07/20/2023 1 4
    3 New 07/14/2023 0 1 3
    3 Development 07/17/2023 1 2
    4 New 07/16/2023 0 1 2
    4 Development 07/18/2023 0 2 3
    4 Done 07/21/2023 1 4
    5 New 07/17/2023 0 1 2
    5 Development 07/19/2023 0 2 3
    5 Done 07/22/2023 1 4
    6 New 07/17/2023 0 1 15
    6 Development 08/01/2023 0 2 14
    6 Done 08/15/2023 1 4
    7 New 07/17/2023 0 1 15
    7 Done 08/01/2023 1 4
    8 New 07/17/2023 0 1 6
    8 Development 07/23/2023 0 2 19
    8 Done 08/11/2023 1 4