cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shefalinishad11
Helper I
Helper I

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
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)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors