Forum Discussion

PJVisscher89's avatar
PJVisscher89
Frequent Visitor
2 years ago
Solved

Calculate metrics on day-by-day Azure DevOps board export

Hello, 
I am struggling with the following and could use some help

 

Situation:

I have an export from Azure DevOps with the day-by-day status of PBIs since creation until day of completion. I am trying to get insight into the following:

1. PBIs that are overdue (Target Date > Completed Date or 2024-05-31 if not completed), filterable to area or period

2. Lead Time and Cycle time, to also be able to calculate averages, top 25%

3. PBIs that have gone backwards in status (e.g. from Completed to In Progress) 

 

Issues:

[1] With calculated columns returning 0 or 1 if conditions are met, each row gets a 1 so a matrix table shows 5 for an overdue item with 5 rows of data. My countrows measure did the same. I guess it can be solved with Max, distinct, or an is current column?

[2] I get the right value in a table per PBI with show max setting, but doesn't work as intended with a table per area.

[3] I have no clue how to write a measure for it. There are only 3 possible statusses so it would need to check the order of a status of a row against those with earlier dates, conditioned that it's all the same PBI.

 

--

My Trial Table. Last 6 columns to be made into measure/calc column. Below expected result

AreaWork item IDStateState CategoryCreated DateClosed DateTarget DateDateIs CurrentLead TimeCycle TimeOverdueOverdue_DaysBackwards
A1NewProposed1-1-20244-1-20243-1-20241-1-2024FALSE     
A1CommittedIn Progress1-1-20244-1-20243-1-20242-1-2024FALSE     
A1CommittedIn Progress1-1-20244-1-20243-1-20243-1-2024FALSE     
A1DoneCompleted1-1-20244-1-20243-1-20244-1-2024TRUE43110
A2NewProposed1-1-20244-1-2024 1-1-2024FALSE     
A2CommittedIn Progress1-1-20244-1-2024 2-1-2024FALSE     
A2DoneCompleted1-1-20244-1-2024 3-1-2024TRUE320 0
A3NewProposed1-1-2024 2-1-20241-1-2024FALSE     
A3CommittedIn Progress1-1-2024 2-1-20242-1-2024FALSE     
A3CommittedIn Progress1-1-2024 2-1-20243-1-2024FALSE     
A3CommittedIn Progress1-1-2024 2-1-20244-1-2024TRUE43120
A4NewProposed1-1-2024  1-1-2024FALSE     
A4DoneCompleted1-1-20242-1-20245-1-20242-1-2024TRUE210 0
B5NewProposed1-1-2024  1-1-2024FALSE     
B5NewProposed1-1-2024  2-1-2024FALSE     
B5CommittedIn Progress1-1-2024  3-1-2024FALSE     
B5DoneCompleted1-1-20244-1-20242-1-20244-1-2024TRUE42120
B6CommittedIn Progress1-1-2024  1-1-2024FALSE     
B6NewProposed1-1-2024  2-1-2024FALSE     
B6NewProposed1-1-2024  3-1-2024TRUE310 1
B7DoneCompleted1-1-20241-1-20242-1-20241-1-2024FALSE     
B7CommittedIn Progress1-1-20241-1-20242-1-20242-1-2024FALSE     
B7CommittedIn Progress1-1-20241-1-20242-1-20243-1-2024TRUE310 1
B8NewProposed1-1-20247-1-202410-1-20241-1-2024FALSE     
B8CommittedIn Progress1-1-20247-1-202410-1-20242-1-2024FALSE     
B8CommittedIn Progress1-1-20247-1-202410-1-20243-1-2024FALSE     
B8CommittedIn Progress1-1-20247-1-202410-1-20244-1-2024FALSE     
B8CommittedIn Progress1-1-20247-1-202410-1-20245-1-2024FALSE     
B8CommittedIn Progress1-1-20247-1-202410-1-20246-1-2024FALSE     
B8DoneCompleted1-1-20247-1-202410-1-20247-1-2024TRUE760 0

 

Expected result:

Area# OverdueAvg OverdueAvg Lead TimeAvg Cycle Time# Backwards
A21,53,252,250
B124,252,52
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi PJVisscher89 ,
    Thanks for lbendlin  reply.
    Here some steps that I want to share, you can check them if they suitable for your requirement.
    Create two calculated column:

    Overdue = 
VAR _completedDate = 
CALCULATE(
    MAX('Table'[Date]),
    ALLEXCEPT(
        'Table',
        'Table'[Work item ID]
        )
)
VAR _targetDate = 
CALCULATE(
    MAX('Table'[Target Date]),
    ALLEXCEPT(
        'Table',
        'Table'[Work item ID]
    )
)
VAR _date = 
IF(
    _targetDate = BLANK(),
    DATE(2024,5,31),
    _targetDate
)
RETURN
IF(
    _completedDate > _date && _completedDate = 'Table'[Date].[Date],
    1,
    BLANK()
)
    Overdue_Days = 
VAR _completedDate = 
CALCULATE(
    MAX('Table'[Date]),
    ALLEXCEPT(
        'Table',
        'Table'[Work item ID]
    )
)
VAR _targetDate = 
CALCULATE(
    MAX('Table'[Target Date]),
    ALLEXCEPT(
        'Table',
        'Table'[Work item ID]
    )
)
RETURN
IF(
    'Table'[Overdue] = 1,
    DATEDIFF(_targetDate,_completedDate,DAY),
    BLANK()
)

    Using Aggregation on Columns on Views

    Best regards,
    Albert He

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

     



3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi PJVisscher89 ,
    Thanks for lbendlin  reply.
    Here some steps that I want to share, you can check them if they suitable for your requirement.
    Create two calculated column:

    Overdue = 
VAR _completedDate = 
CALCULATE(
    MAX('Table'[Date]),
    ALLEXCEPT(
        'Table',
        'Table'[Work item ID]
        )
)
VAR _targetDate = 
CALCULATE(
    MAX('Table'[Target Date]),
    ALLEXCEPT(
        'Table',
        'Table'[Work item ID]
    )
)
VAR _date = 
IF(
    _targetDate = BLANK(),
    DATE(2024,5,31),
    _targetDate
)
RETURN
IF(
    _completedDate > _date && _completedDate = 'Table'[Date].[Date],
    1,
    BLANK()
)
    Overdue_Days = 
VAR _completedDate = 
CALCULATE(
    MAX('Table'[Date]),
    ALLEXCEPT(
        'Table',
        'Table'[Work item ID]
    )
)
VAR _targetDate = 
CALCULATE(
    MAX('Table'[Target Date]),
    ALLEXCEPT(
        'Table',
        'Table'[Work item ID]
    )
)
RETURN
IF(
    'Table'[Overdue] = 1,
    DATEDIFF(_targetDate,_completedDate,DAY),
    BLANK()
)

    Using Aggregation on Columns on Views

    Best regards,
    Albert He

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

     



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

    Your expected result doesn't seem to correlate to your sample data.

     

    Please provide the sample data as it comes from Azure DevOps, and indicate the expected result based on that sample data.