Search value of next date

[frankhofmans]

hi all,

I have a table with omission percentages (of sick employees) and the start date. For example:

Case ID	Start Date	Absenteeism percentage
C001	10-04-2023	100
C001	25-04-2023	30
C001	27-04-2023	0
C002	15-04-2023	100
C002	20-04-2023	90
C002	30-04-2023	0

I want to add a column with the end date. In the case of C001, the end date of the first row has to be 24-04-2023 (start date of next row -1), the end of the second row has to be 26-04-2023. In the third row, the end date has to be 27-04-2023 (same as the start date, because there is no "later" row of that caseID). Is there a opposite of the earlier function (my first instinct to solve this), or do you guys have another solution (plus formula)?

Many thanks in advance,

Regards,

Frank

[v-zhangti · Accepted solution]

Hi, @frankhofmans 

You can try the following methods.

End Date = 
Var _nextdate=MINX(FILTER('Table',[Start Date]>EARLIER('Table'[Start Date])&&[Case ID]=EARLIER('Table'[Case ID])),[Start Date])
Return
IF(_nextdate=BLANK(),[Start Date],_nextdate-1)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.