Forum Discussion

ValerienSegard's avatar
ValerienSegard
Frequent Visitor
5 years ago
Solved

Completed Work by cascade (Issue -> Epic -> Objective)

Hello everybody

 

I would like to use our Azure Devops to monitor the progress of our projects.

 

The architecture is as follows:
Objective> Epic> Issue

 

Objective can have many epics, but one epic is only linked to one objective.

Epic can have many Issues, but one issue is only linked to one Epic.

 

In our kanban, the completion of the work is done on the Issues (scale from 0 to 100).

 

I would like to be able to calculate the impact of the completion of issues on the linked Epic, and therefore on the linked objective.

Knowing that we must weigh with the duration of the Issues.

Ex: Completion of an Epic =% Completion Issue (s) * Duration Issue (s) / Sum (Duration Issue (s))

 

Ex: Completion of an Objective=% Completion Issue (s) * Duration Issue (s) / Sum (Duration Issue (s))

OR Completion of an Objective=% Completion Epic(s) * Duration Issue (s) / Sum (Duration Epic(s)) ???

 

A very big thank you in advance

 

IdTitleWork TypeStart DateEnd DateCompletionParent ID
1Objective 1Objective01/01/202131/12/2021??? 
2Epic 1Epic01/01/202131/03/2021???1
3Issue 1Issue01/01/202101/02/2021752
4Issue 2Issue15/03/202131/03/2021302
Need Help
  • dedelman_clng's avatar
    dedelman_clng
    5 years ago

    Hi ValerienSegard  - 

     

    First thing to do is see if you can change your data model (inside PBI) to have Objective, Epic and Issue in 3 separate tables with 1-to-many relationships (1 Objective to Many Epic, 1 Epic to Many Issue). If you can do that, it will make things much easier.

     

     

    Once you do that, create a column on Issue for the IssueDuration

     

    IssueDuration = DATEDIFF(Issues[Start Date], Issues[End Date], DAY)

     

    Then a measure for the rolled up Completion %

     

    Completion Pct =
DIVIDE (
    SUMX ( Issues, Issues[IssueDuration] * ( Issues[Completion] / 100 ) ),
    SUM ( Issues[IssueDuration] ),
    0
)

     

    Hope this helps

    David

     

2 Replies

  • ValerienSegard's avatar
    ValerienSegard
    Frequent Visitor
    5 years ago

    That's exactly what i did this afternoon before i saw your answer 🙂 AND IT WORKS

     

    Thank you !

  • dedelman_clng's avatar
    dedelman_clng
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi ValerienSegard  - 

     

    First thing to do is see if you can change your data model (inside PBI) to have Objective, Epic and Issue in 3 separate tables with 1-to-many relationships (1 Objective to Many Epic, 1 Epic to Many Issue). If you can do that, it will make things much easier.

     

     

    Once you do that, create a column on Issue for the IssueDuration

     

    IssueDuration = DATEDIFF(Issues[Start Date], Issues[End Date], DAY)

     

    Then a measure for the rolled up Completion %

     

    Completion Pct =
DIVIDE (
    SUMX ( Issues, Issues[IssueDuration] * ( Issues[Completion] / 100 ) ),
    SUM ( Issues[IssueDuration] ),
    0
)

     

    Hope this helps

    David