continuous date range

[Will1]

I am trying to create a variable in Business Objects that will display a 'Y' for each employee based on some logic relating to the start and end dates. For each employee, I want to find the most recent end date and then from that date work backwards to find the earliest start date. This range must be continuous with no more than 1 day break between start and end date.

For employee No 1, the most recent end date is 30/11/2024, working back the earliest start date in the continuous date range would be 01/02/2023, so I would want to see a 'Y' against the row that has start date of 01/02/2023.

For employee No 2, the most recent end date is 30/04/2025, working back the earliest start date in the continuous date range would be 01/05/2024, so I would want to see a 'Y' against the row that has start date of 01/05/2024.

For employee No 3, the most recent end date is 30/11/2024, working back the earliest start date in the continuous date range would be 01/04/2023, so I would want to see a 'Y' against the row that has start date of 01/04/2023.

It is important that the solution only works based on the most recent end date because there can be older continuous dates in the range but I am not interested in those.

Thank you for your help

Will

[nachtaktiv]

you can try with min() or max() or first() or last() to find the requested dates
and you have to use the context of IN([Employee No]) in your formula to eliminate all other dimensions in your table