Calculating max date for a group of a previous task

[elukianic]

Hello All,

I am trying to have a calculated column where I am getting a max end date for the previous taskno. (task numbers are not sequencial, its just previous task is always smaller than a current task). This calculation will be done per group orderid, resseq resinstanceid and task no.

So in this example below. For taskno 3 it should be null in calculated column, for taskno 4 it should be 11/9/2021 since the previous task is number 3 and the max end date for that group is 11/9/2021. For task no 6 it should be 11/23/2021, because previous task number is 4 and the max end date in that group is 11/23/2021. Thank you all for help!

[elukianic]

Worked like a charm! Thank you!

[ryan_mayu]

you are welcome

[ryan_mayu · Accepted solution]

@elukianic 

pls try this

Column = 
VAR _task= maxx(FILTER('Table (2)','Table (2)'[orderid]=EARLIER('Table (2)'[orderid])&&'Table (2)'[taskno]<EARLIER('Table (2)'[taskno])),'Table (2)'[taskno])
return maxx(FILTER('Table (2)','Table (2)'[orderid]=EARLIER('Table (2)'[orderid])&&'Table (2)'[taskno]=_task),'Table (2)'[endtime])