I think i got it as long as i can guarantee the correct sort order .
If anyone looking for a similar task:
IF(EmployeeID = peek('EmployeeID') and isnull(EmpRelEndDate) AND EmpRelStartDate <> peek('EmpRelStartDate'),
EmpRelEndDate) as NewEnd
Order by EmployeeID, EmpRelStartDate desc, Period desc;
Here is a really simple example of what I think you are trying to do, for this example I have created a new field called NewEmpRelEndDate.
Hope this helps.
159788.qvw 153.5 K