Calculating end date and duration for multiple tasks AND multiple projects

[vivi_nainai2021]

Hi PBI experts! I'm trying to calculate the end date and duration (between those two dates) for multiple projects that have multiple tasks within themselves. My table looks like: 

In the project name column, I have around 10 projects. All projects go through tasks and the start date of those tasks are in the start date column. First, what I need is to calculate the end date for each task for each project. So if project ARIDA went from gate 1 to gate 3, the end date of gate 1 would be the start date of gate 3 and so on. Second, I need to calcualte the duration in years between start date and end date for each task for each project, which would be easy if I can calculate the end date column. 

Does anyone have the solution? 

Cheers!

[Anonymous]

Hi @vivi_nainai2021 ,

Please try this formulas:

end date =
CALCULATE (
    MIN ( 'Table'[start date] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[project name] ),
        'Table'[start date] > EARLIER ( 'Table'[start date] )
    )
)

durations = DATEDIFF('Table'[start date],'Table'[end date],DAY)

Best Regards,

Jay

[NickolajJessen]

For a Measure solution feel free to check out the:
"Department increase from previous date" .Pbix here:
https://drive.google.com/drive/folders/1AjePk7NUXAloXHr42BEa2lDOr5dQr2Wg

[amitchandak · Accepted solution]

@vivi_nainai2021 , You can create a new column like

End date = Minx(filter(Table, [Project name] =earlier([Project name]) && [Start Date] > earlier([Start Date]) ), [Start Date])

Diff in days = datediff([Start date],[End Date], day)

[vivi_nainai2021]

Thank you @amitchandak , but I'm not getting the output I need. This is what I get but I need the start date of a succeeding task to the be end date for the previous one:

Can the previous formula be tweaked?