Forum Discussion
Calculating end date and duration for multiple tasks AND multiple projects
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!
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)
4 Replies
- amitchandak
Super User
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)
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?
NickolajJessenSolution Sage
For a Measure solution feel free to check out the:
"Department increase from previous date" .Pbix here:
https://drive.google.com/drive/folders/1AjePk7NUXAloXHr42BEa2lDOr5dQr2WgHi 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
