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

[ValerienSegard]

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

Id	Title	Work Type	Start Date	End Date	Completion	Parent ID
1	Objective 1	Objective	01/01/2021	31/12/2021	???
2	Epic 1	Epic	01/01/2021	31/03/2021	???	1
3	Issue 1	Issue	01/01/2021	01/02/2021	75	2
4	Issue 2	Issue	15/03/2021	31/03/2021	30	2

[ValerienSegard]

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

Thank you !

[dedelman_clng · Accepted solution]

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