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

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)
    )