Calculate Days Between Tasks in a Project

[RaymondNG]

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. Some of them have completed all tasks and some have only completed a few. Below is a sample of my data with the project name, start date, completion date, and name of the task. 

For example, if Task 3 is completed on 08/24/2021 and Task 4 is started on 09/01/2021, I need to know the difference in days between those two dates. I also need to know the difference in days for all subsequent tasks. 

I could write individual DAX to calculate the difference between each Task but I'm trying to write a single DAX formula to encompass all tasks. 

Project    Start Date     Compl. Date  Task Name

Project A	8/24/2021	8/24/2021	Task 3
Project A	09/01/2021	09/04/2021	Task 4
Project A	09/06/2021	09/15/2021	Task 5
Project A	10/7/2021	10/7/2021	Task 6
Project A	9/21/2021	9/21/2021	Task 7
Project A	12/17/2021	12/17/2021	Task 8
Project A	2/28/2022	3/4/2022	Task 9
Project A	2/14/2022	2/14/2022	Task 10
Project A	1/27/2022	1/27/2022	Task 11
Project A	3/4/2022	3/4/2022	Task 12
Project A	3/14/2022	3/14/2022	Task 13
Project A	08/01/2021	08/14/2021	Task 1
Project A	8/20/2021	8/20/2021	Task 2
Project B	8/17/2021	8/17/2021	Task 3
Project B	10/22/2021	10/31/2021	Task 7
Project C	08/18/2021	08/19/2021	Task 1
Project C	09/14/2021	10/20/2021	Task 2
Project C	1/4/2022	1/4/2022	Task 3
Project C	1/6/2022	1/6/2022	Task 5
Project C	1/13/2022	1/13/2022	Task 6
Project C	1/19/2022	1/19/2022	Task 7
Project C	2/11/2022	2/11/2022	Task 8
Project C	2/25/2022	2/25/2022	Task 9
Project C	2/18/2022	2/18/2022	Task 10
Project C	2/28/2022	2/28/2022	Task 11
Project C	3/4/2022	3/4/2022	Task 12
Project C	3/21/2022	3/21/2022	Task 13
Project D	8/6/2021	8/6/2021	Task 1
Project D	8/17/2021	8/17/2021	Task 2
Project D	8/30/2021	8/30/2021	Task 3
Project D	09/01/2021	09/10/2021	Task 4
Project D	09/11/2021	10/01/2021	Task 5
Project D	10/14/2021	10/14/2021	Task 6
Project D	1/25/2022	1/28/2022	Task 7
Project D	01/30/2022	02/02/2022	Task 8
Project D	02/08/2022	02/15/2022	Task 9
Project D	02/16/2022	02/17/2022	Task 10
Project D	02/20/2022	02/28/2022	Task 11
Project D	03/01/2022	03/04/2022	Task 12
Project D	3/7/2022	3/7/2022	Task 13

[mahoneypat]

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]

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?

[mahoneypat]

Please clarify your needed logic, as you have some that are out of order. For example, for Project A, Task 7 starts before Task 6 ends.

Pat

[RaymondNG]

Here is some cleaned up data with corrected dates. I'm trying to figure out how many days pass between the end of one task and the start of the subsequent task for each project. I found DAX to calculate the total project time on a different community page. I tried adding another variable for the Task Name so it would calculate the days between each tasks for individual projects but it returned blanks (see below).  I'm new to Power BI so I may be missing something. 

Project Name	Start Date	Complete Date	Task Name
Project A	8/1/2021	8/14/2021	Task 1
Project A	8/20/2021	8/20/2021	Task 2
Project A	8/24/2021	8/24/2021	Task 3
Project A	9/1/2021	9/4/2021	Task 4
Project A	9/6/2021	9/15/2021	Task 5
Project A	10/7/2021	10/7/2021	Task 6
Project A	11/21/2021	11/21/2021	Task 7
Project A	12/17/2021	12/17/2021	Task 8
Project A	1/27/2022	1/27/2022	Task 9
Project A	2/14/2022	2/14/2022	Task 10
Project A	2/28/2022	3/3/2022	Task 11
Project A	3/4/2022	3/4/2022	Task 12
Project A	3/14/2022	3/14/2022	Task 13
Project A	3/16/2022	3/20/2022	Task 14
Project B	7/7/2021	7/9/2021	Task 1
Project B	7/20/2021	7/31/2021	Task 2
Project B	8/17/2021	8/17/2021	Task 3
Project B	8/20/2021	8/31/2021	Task 4
Project B	9/1/2021	9/15/2021	Task 5
Project B	9/30/2021	10/1/2021	Task 6
Project B	10/22/2021	10/31/2021	Task 7
Project C	8/18/2021	8/19/2021	Task 1
Project C	9/14/2021	10/20/2021	Task 2
Project C	1/4/2022	1/4/2022	Task 3
Project C	1/5/2022	1/5/2022	Task 4
Project C	1/6/2022	1/6/2022	Task 5
Project C	1/13/2022	1/13/2022	Task 6
Project C	1/19/2022	1/19/2022	Task 7
Project C	2/11/2022	2/11/2022	Task 8
Project C	2/25/2022	2/25/2022	Task 9
Project C	2/18/2022	2/18/2022	Task 10
Project C	2/28/2022	2/28/2022	Task 11
Project C	3/4/2022	3/4/2022	Task 12
Project C	3/21/2022	3/21/2022	Task 13
Project D	8/6/2021	8/6/2021	Task 1
Project D	8/17/2021	8/17/2021	Task 2
Project D	8/30/2021	8/30/2021	Task 3
Project D	9/1/2021	9/10/2021	Task 4
Project D	9/11/2021	10/1/2021	Task 5
Project D	10/14/2021	10/14/2021	Task 6
Project D	1/25/2022	1/28/2022	Task 7
Project D	1/30/2022	2/2/2022	Task 8
Project D	2/8/2022	2/15/2022	Task 9
Project D	2/16/2022	2/17/2022	Task 10
Project D	2/20/2022	2/28/2022	Task 11
Project D	3/1/2022	3/4/2022	Task 12
Project D	3/7/2022	3/7/2022	Task 13

Days Between Tasks =

VAR Task = MAX(Project[Task Name])

VAR Project = MAX(Project[Project Name])

VAR _Start =

MINX(

FILTER(

ALL(mavenlink__Mavenlink_Task__c),

Project[Project Name] = Project &&

Project[Task Name] = Task &&

Project[Task Start Date].[Date] <> BLANK()

),

Project[Task Start Date].[Date]

)

VAR _Finish =

MAXX(

FILTER(

ALL(mavenlink__Mavenlink_Task__c),

Project[Project Name] = Project &&

Project[Task Name] = Task &&

Project[Task Complete Date].[Date] <> BLANK()

),

Project[Task Complete Date].[Date]

)

RETURN

IF(

ISBLANK(_Finish),

BLANK(),

DATEDIFF(_Start, _Finish, DAY)

)