Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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.

1 ACCEPTED SOLUTION
Geradav
Responsive Resident
Responsive Resident

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 )

View solution in original post

6 REPLIES 6
Geradav
Responsive Resident
Responsive Resident

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 )

It worked, thank you so much to help.

Geradav
Responsive Resident
Responsive Resident

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

 

Geradav_0-1611226363309.png

 

Let us know if that works for you

 

David

 

amitchandak
Super User
Super User

@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
Super User
Super User

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

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)
Screenshot_1.jpg

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.