Forum Discussion

shefalinishad11's avatar
shefalinishad11
Icon for Helper I rankHelper I
5 years ago
Solved

Calculate task end date from task start date and project wise

Hey Community,

 

I am creating a milestone tracker, and I need little help here:

Project

Task

Task Start Date

Task End Date

P1

T1

12/2/21

15/2/21

P1

T2

15/2/21

15/2/21

P2

T1

12/3/21

15/3/21

P2

T2

15/3/21

20/3/21

P2

T3

20/3/21

20/3/21

 

I need to work on the DAX function that will automatically calculate the task end date with the next task start, and if the project change, it will consider the task start date as the end date.

Need Help
Tips and Tricks
  • Geradav's avatar
    Geradav
    5 years ago

    Here is an alternative adapted from amitchandak 

    EndDate_V2 =
VAR project = TableTasks[Project]
VAR TaskStartDate = TableTasks[TaskStartDate]
VAR nextTaskDate =
    MINX (
        FILTER (
            TableTasks,
            TableTasks[Project] = project
                && TableTasks[TaskStartDate] > TaskStartDate
        ),
        TableTasks[TaskStartDate]
    )
RETURN
    IF ( ISBLANK ( nextTaskDate ), TaskStartDate, nextTaskDate )

6 Replies

  • Geradav's avatar
    Geradav
    Icon for Responsive Resident rankResponsive Resident
    5 years ago

    Here is an alternative adapted from amitchandak 

    EndDate_V2 =
VAR project = TableTasks[Project]
VAR TaskStartDate = TableTasks[TaskStartDate]
VAR nextTaskDate =
    MINX (
        FILTER (
            TableTasks,
            TableTasks[Project] = project
                && TableTasks[TaskStartDate] > TaskStartDate
        ),
        TableTasks[TaskStartDate]
    )
RETURN
    IF ( ISBLANK ( nextTaskDate ), TaskStartDate, nextTaskDate )
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    shefalinishad11 , Try a new column like

     


    New column =
    var _min= maxx(filter(Table, [project] =earlier([project]) && [Task Start Date] > [Task Start Date] ),[Task Start Date])
    return
    if(isblank(_min), [Task Start Date] ,_min)

    • shefalinishad11's avatar
      shefalinishad11
      Icon for Helper I rankHelper I
      5 years ago

      Hi amitchandak ,

       

      Thanks for looking out.

       

      The column work but it showing the start date in the end date column. It supposed to pick the start date of the next task as the end date for that particular project. If the project last task then the end date should be the same as the start date. Sharing screenshot. 

      Task End Date = Var _min = MAXX(FILTER('Table 1','Table 1'[Project] = EARLIER('Table 1'[Project])&&'Table 1'[Task Start Date]> 'Table 1'[Task Start Date]),'Table 1'[Task Start Date])
      return
      IF(ISBLANK(_min),'Table 1'[Task Start Date],_min)
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    shefalinishad11 , Th logic I applied, If in the smae project I get next date, That will end date of this step, else start date and enddate.

     

    Can you copy paste data from excel. It coming all in one line

  • Geradav's avatar
    Geradav
    Icon for Responsive Resident rankResponsive Resident
    5 years ago

    shefalinishad11 

    Here is a tentative solution as a calculated column

    EndDate =
VAR ProjectID = TableTasks[Project]
VAR TaskIndex =
    RIGHT ( TableTasks[Task], LEN ( TableTasks[Task] ) - 1 )
VAR Result =
    CALCULATE (
        MAX ( TableTasks[TaskStartDate] ),
        FILTER (
            TableTasks,
            TableTasks[Project] = ProjectID
                && TableTasks[Task] = "T" & ( TaskIndex + 1 )
        )
    )
RETURN
    IF ( ISBLANK ( Result ), TableTasks[TaskStartDate], Result )

     

     

    Let us know if that works for you

     

    David