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

Bifinity_75's avatar
Bifinity_75
Solution Sage
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

SAW's avatar
SAW
Frequent Visitor
3 years ago

Thanks, this got me real close.  I had to add an AND statement top the Idle Time filter because I was getting Idle time calculations between the last completion date of one project and the start date of the next project.  Other than that I made some tweaks since the dataset I provided as an example lacked a lot of detail from the actual dataset.

 

I really appreciate your help with this; I know I wouldn't have come up with a solution on my own!