Forum Discussion
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. What I'm trying to figure out for each project is the total number of days during which no phase is being worked.
For example using the table above, for the first project the Assessment phase began on 7/22 and ran until 8/12. The Unique Event and Testing phases ran concurrent with the Assessement phase (started after Assessment and completed on the same day), then there 3 idle days before the Completion phase started. So for this project there were 3 idle days between the beginning of the first phase and the end of the last phase.
The middle project has no idle days since all of the phases overlap in time.
The last project above had 7 days of idle time between the Initiation and Assessment phases and then 1 more day between the completion of the Assessment phase and the start of the Completion phase, for a total of 8 idle days.
Can anyone help me write a measure that calculates the total idle time for a project? I've tried Greg Deckler's Mean Time Between Failure (MTBF) and Power BI approach, but that is used for sequential events and I haven't been able to adapt if for this use.
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
4 Replies
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.
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):
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
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!
