Forum Discussion

michaelwatts80's avatar
michaelwatts80
Regular Visitor
2 years ago
Solved

Azure DevOps Time In Board Column

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:

WorkItemIdColumnRevisionChangedDateChangedDateTime
123456In Development58/2/20248/2/24 11:29 PM
123456In Development48/2/20248/2/24 11:20 PM
123456In Development38/2/20248/2/24 11:19 PM
123456In Development28/2/20248/2/24 11:09 PM
123456In Development18/2/20248/2/24 11:00 PM
123456Ready for Development57/29/20247/29/24 11:29 PM
123456Ready for Development47/29/20247/29/24 11:20 PM
123456Ready for Development37/29/20247/29/24 11:19 PM
123456Ready for Development27/29/20247/29/24 11:09 PM
123456Ready for Development17/29/20247/29/24 11:00 PM


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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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


     

     
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      ValeriiaG
      New Member
      2 years ago

      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.