Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
PJVisscher89
Frequent Visitor

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

‎06-13-2024 02:58 AM

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
Labels:
Message 1 of 4
373 Views
0
1 ACCEPTED SOLUTION
v-heq-msft
Community Support
Community Support

‎06-23-2024 11:24 PM

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

Calculate metrics on day-by-day Azure DevOps board export.pbix
Message 3 of 4
263 Views
3 REPLIES 3
v-heq-msft
Community Support
Community Support

‎06-23-2024 11:24 PM

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

 



Calculate metrics on day-by-day Azure DevOps board export.pbix
Message 3 of 4
264 Views
PJVisscher89
Frequent Visitor
In response to v-heq-msft

‎06-26-2024 01:10 AM

That's very helpful yes, thanks!

Message 4 of 4
231 Views
0
lbendlin
Super User
Super User

‎06-13-2024 06:01 PM

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.

Message 2 of 4
317 Views
0

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

View All