Forum Discussion

RaymondNG's avatar
RaymondNG
Frequent Visitor
4 years ago

Calculate Days Between Tasks in a Project

I am trying to calculate how many days there are between the completion of one task and the start of another. Each project has tasks ranging in number from 1 to 14. I'm working with multiple projects...
Need Help
mahoneypat's avatar
mahoneypat
Microsoft Employee
4 years ago

I came up with a more complicated one first, but the second approach got the same results with your data. The 2nd just finds the overall # of days and subtracts the total time for task activity.

 

Wait Time =
VAR completedtasks =
CALCULATETABLE(
SUMMARIZE( Tasks, Tasks[Project Name], Tasks[Task Name], Tasks[Complete Date] ),
NOT ( ISBLANK( Tasks[Complete Date] ) )
)
VAR withnextstart =
ADDCOLUMNS(
completedtasks,
"cNextStart",
VAR thiscompl = Tasks[Complete Date]
RETURN
CALCULATE(
MIN( Tasks[Start Date] ),
ALL( Tasks ),
VALUES( Tasks[Project Name] ),
Tasks[Start Date] > thiscompl
)
)
RETURN
SUMX(
FILTER( withnextstart, NOT ( ISBLANK( [cNextStart] ) ) ),
INT( [cNextStart] - Tasks[Complete Date] )
)

 

Wait Time 2 =
VAR totaltime =
SUMX( Tasks, INT( Tasks[Complete Date] - Tasks[Start Date] ) )
VAR overalltime =
INT( MAX( Tasks[Complete Date] ) - MIN( Tasks[Start Date] ) )
RETURN
overalltime - totaltime

 

Pat

  • RaymondNG's avatar
    RaymondNG
    Frequent Visitor
    4 years ago

    Thank you, I used the 2nd one and it is working. Is there a way to modify it so I can see the wait time between each task? For example, seeing the wait time between Task 6 and Task 7?