Forum Discussion

SAW's avatar
SAW
Frequent Visitor
3 years ago
Solved

Idle time

I have a list of projects that can have 1-5 different phases over the course of the project.  These phases can run in any order for a particular project, including concurrently with other phases.  Wh...
Need Help
  • Bifinity_75's avatar
    Bifinity_75
    3 years ago

    Hi SAW , try this:

     

    - Insert a Index column in Power Query

    - Create a calculate column with this formule:

    Result = 
VAR Star_d=CALCULATE(MAX('Table'[Phase Start]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])+1))
VAR Completed_d=CALCULATE(MAX('Table'[Phase Completed]),ALLEXCEPT('Table','Table'[Project]),'Table'[Phase]<>"Completion")
return
SWITCH(
    TRUE(),
'Table'[Phase Completed]-Star_d>0,0,
Star_d>Completed_d,Star_d-Completed_d-1,
Star_d-'Table'[Phase Completed]-1
)

    With the small sample you have sent, it works:

     

    Best regards

v-yadongf-msft's avatar
v-yadongf-msft
Community Support
3 years ago

Hi SAW 

 

I'm a little confused about the calculation logic of the last project.

 

It seems that total are 13 days. 7 days from 7/12 to 7/20 and 6 days from 8/12 to 8/19.

 

Did I understand your demands correctly ?

 

Best regards,

Yadong Fang

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

SAW's avatar
SAW
Frequent Visitor
3 years ago

v-yadongf-msft For the last project, the Testing phase is concurrent with the Assessment phase and therefore doesn't contribute to idle time.  The second group of days resulting in a day of idle time comes between the end of the Assessment phase and the beginning of the Completion phase (see highlighted areas below):