Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PJVisscher89
Frequent Visitor

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
1 ACCEPTED SOLUTION
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1719210144283.png

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

 



View solution in original post

3 REPLIES 3
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1719210144283.png

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

 



That's very helpful yes, thanks!

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors