Azure DevOps Time In Board Column

[michaelwatts80]

I have pulled data from the WorkItemRevisions table and expanded the BoardLocations table to get all Revisions for WorkItems with the ColumnName from the BoardLocations table. I am trying to calculate the number of Days and Hours a Work Item has been in each ColumnName. 

Data Example:

WorkItemId	Column	Revision	ChangedDate	ChangedDateTime
123456	In Development	5	8/2/2024	8/2/24 11:29 PM
123456	In Development	4	8/2/2024	8/2/24 11:20 PM
123456	In Development	3	8/2/2024	8/2/24 11:19 PM
123456	In Development	2	8/2/2024	8/2/24 11:09 PM
123456	In Development	1	8/2/2024	8/2/24 11:00 PM
123456	Ready for Development	5	7/29/2024	7/29/24 11:29 PM
123456	Ready for Development	4	7/29/2024	7/29/24 11:20 PM
123456	Ready for Development	3	7/29/2024	7/29/24 11:19 PM
123456	Ready for Development	2	7/29/2024	7/29/24 11:09 PM
123456	Ready for Development	1	7/29/2024	7/29/24 11:00 PM

I cannot figure out how to properly calculate for Days and Hours. Any help would be appreciated.

[Anonymous · Accepted solution]

Hi @michaelwatts80 , hello, rajendraongole1, thank you for your prompt reply!

Please create two measures as shown below:

TotalTimeInColumnDays = 
   VAR WorkItemId = MAX('Table'[WorkItemId])
   VAR CurrentColumn = SELECTEDVALUE('Table'[Column])
   VAR MinTime =
       CALCULATE(
           MIN('Table'[ChangedDateTime]),
           FILTER(
               ALL('Table'),
               'Table'[WorkItemId] = WorkItemId &&
               'Table'[Column] = CurrentColumn
           )
       )
   VAR MaxTime =
       CALCULATE(
           MAX('Table'[ChangedDateTime]),
           FILTER(
               ALL('Table'),
               'Table'[WorkItemId] = WorkItemId &&
               'Table'[Column] = CurrentColumn
           )
       )
       VAR diff=DATEDIFF(MinTime, MaxTime, SECOND)
   VAR TotalSeconds = diff
   VAR Days = ROUND(DIVIDE(TotalSeconds, 86400), 2)
   RETURN
       Days 

TotalTimeInColumnHours = 
   VAR Hours = [TotalTimeInColumnDays]*24
   RETURN
       Hours 

 Result:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ValeriiaG]

Try using the Time in State for Azure DevOps extension to get Time In Board Column. In addition to calculating cycle time, transition count, status count and more. You can customize your work calendars to suit your needs. And easily export this data. I think you’ll find it helpful.
 

[rajendraongole1]

Hi @michaelwatts80 - create below measure for previous changed date as below:

PreviousChangedDateTime =

VAR CurrentWorkItemId = MAX(WorkItemRevisions[WorkItemId])

VAR CurrentRevision = MAX(WorkItemRevisions[Revision])

RETURN

    CALCULATE(

        MAX(WorkItemRevisions[ChangedDateTime]),

        FILTER(

            ALL(WorkItemRevisions),

            WorkItemRevisions[WorkItemId] = CurrentWorkItemId &&

            WorkItemRevisions[Revision] < CurrentRevision

        )

    )

To calculate Duration in days use below measure:

DurationInDays =

VAR CurrentDateTime = MAX(WorkItemRevisions[ChangedDateTime])

VAR PreviousDateTime = [PreviousChangedDateTime]

RETURN

    IF(

        ISBLANK(PreviousDateTime),

        0,

        DATEDIFF(CurrentDateTime,PreviousDateTime , DAY)

    )

To get the duration in hours 

DurationInHours =

VAR CurrentDateTime = MAX(WorkItemRevisions[ChangedDateTime])

VAR PreviousDateTime = [PreviousChangedDateTime]

RETURN

    IF(

        ISBLANK(PreviousDateTime),

        0,

        DATEDIFF(CurrentDateTime,PreviousDateTime , HOUR)

    )

Hope it helps