cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Search value of next date

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)?

Regards,

Frank

1 ACCEPTED SOLUTION
Community Support

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.

Community Support

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.