Calculate task end date from task start date and project wise

[shefalinishad11]

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.

[Geradav · Accepted solution]

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 )

[shefalinishad11]

It worked, thank you so much to help.

[Geradav]

@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

[amitchandak]

@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

[amitchandak]

@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]

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)