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. 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 A8/24/20218/24/2021Task 3
Project A09/01/202109/04/2021Task 4
Project A09/06/202109/15/2021Task 5
Project A10/7/202110/7/2021Task 6
Project A9/21/20219/21/2021Task 7
Project A12/17/202112/17/2021Task 8
Project A2/28/20223/4/2022Task 9
Project A2/14/20222/14/2022Task 10
Project A1/27/20221/27/2022Task 11
Project A3/4/20223/4/2022Task 12
Project A3/14/20223/14/2022Task 13
Project A08/01/202108/14/2021Task 1
Project A8/20/20218/20/2021Task 2
Project B8/17/20218/17/2021Task 3
Project B10/22/202110/31/2021Task 7
Project C08/18/202108/19/2021Task 1
Project C09/14/202110/20/2021Task 2
Project C1/4/20221/4/2022Task 3
Project C1/6/20221/6/2022Task 5
Project C1/13/20221/13/2022Task 6
Project C1/19/20221/19/2022Task 7
Project C2/11/20222/11/2022Task 8
Project C2/25/20222/25/2022Task 9
Project C2/18/20222/18/2022Task 10
Project C2/28/20222/28/2022Task 11
Project C3/4/20223/4/2022Task 12
Project C3/21/20223/21/2022Task 13
Project D8/6/20218/6/2021Task 1
Project D8/17/20218/17/2021Task 2
Project D8/30/20218/30/2021Task 3
Project D09/01/202109/10/2021Task 4
Project D09/11/202110/01/2021Task 5
Project D10/14/202110/14/2021Task 6
Project D1/25/20221/28/2022Task 7
Project D01/30/202202/02/2022Task 8
Project D02/08/202202/15/2022Task 9
Project D02/16/202202/17/2022Task 10
Project D02/20/202202/28/2022Task 11
Project D03/01/202203/04/2022Task 12
Project D3/7/20223/7/2022Task 13
Need Help

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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's avatar
      RaymondNG
      Frequent Visitor
      4 years ago

      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 NameStart DateComplete DateTask Name
      Project A8/1/20218/14/2021Task 1
      Project A8/20/20218/20/2021Task 2
      Project A8/24/20218/24/2021Task 3
      Project A9/1/20219/4/2021Task 4
      Project A9/6/20219/15/2021Task 5
      Project A10/7/202110/7/2021Task 6
      Project A11/21/202111/21/2021Task 7
      Project A12/17/202112/17/2021Task 8
      Project A1/27/20221/27/2022Task 9
      Project A2/14/20222/14/2022Task 10
      Project A2/28/20223/3/2022Task 11
      Project A3/4/20223/4/2022Task 12
      Project A3/14/20223/14/2022Task 13
      Project A3/16/20223/20/2022Task 14
      Project B7/7/20217/9/2021Task 1
      Project B7/20/20217/31/2021Task 2
      Project B8/17/20218/17/2021Task 3
      Project B8/20/20218/31/2021Task 4
      Project B9/1/20219/15/2021Task 5
      Project B9/30/202110/1/2021Task 6
      Project B10/22/202110/31/2021Task 7
      Project C8/18/20218/19/2021Task 1
      Project C9/14/202110/20/2021Task 2
      Project C1/4/20221/4/2022Task 3
      Project C1/5/20221/5/2022Task 4
      Project C1/6/20221/6/2022Task 5
      Project C1/13/20221/13/2022Task 6
      Project C1/19/20221/19/2022Task 7
      Project C2/11/20222/11/2022Task 8
      Project C2/25/20222/25/2022Task 9
      Project C2/18/20222/18/2022Task 10
      Project C2/28/20222/28/2022Task 11
      Project C3/4/20223/4/2022Task 12
      Project C3/21/20223/21/2022Task 13
      Project D8/6/20218/6/2021Task 1
      Project D8/17/20218/17/2021Task 2
      Project D8/30/20218/30/2021Task 3
      Project D9/1/20219/10/2021Task 4
      Project D9/11/202110/1/2021Task 5
      Project D10/14/202110/14/2021Task 6
      Project D1/25/20221/28/2022Task 7
      Project D1/30/20222/2/2022Task 8
      Project D2/8/20222/15/2022Task 9
      Project D2/16/20222/17/2022Task 10
      Project D2/20/20222/28/2022Task 11
      Project D3/1/20223/4/2022Task 12
      Project D3/7/20223/7/2022Task 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)
      )
  • 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?