Forum Discussion
Find next start date in table
Hello everyone,
I have a table that looks like the following: An ID field, a phase, and the date the item was put in that phase (or the phase start date).
I want to add a column that finds the start date of the next phase for that ID. If there is no phase after the current one, the column should say null. I added an example of what that column would look like below.
My ultimate goal is to find how many days are spent in each phase.
This is what I tried that did not work:
phase end date =
VAR CurrentID = SELECTEDVALUE(Table[ID])
VAR CurrentDate = SELECTEDVALUE(Table[Phase Start])
VAR PhaseEndDate = CALCULATE(
MIN(Table[Phase Start]),
FILTER(Table, Table[ID] = CurrentID && Table[Phase Start] > CurrentDate) )
RETURN PhaseEndDate
The above code just returned all blank values.
Thanks!
5 Replies
To get a new column for the next phase time:
next start = CALCULATE(MIN('Table'[Start]), REMOVEFILTERS('Table'[Start]), 'Table'[Start] > EARLIER('Table'[Start]))Thanks vicky_ for your concern about this thread.
Anonymous If you want a calculated column, you can modify your formula like below. This should work.
phase end date = VAR CurrentID = 'Table'[ID] VAR CurrentDate = 'Table'[Phase Start] VAR PhaseEndDate = CALCULATE( MIN('Table'[Phase Start]), FILTER('Table', 'Table'[ID] = CurrentID && 'Table'[Phase Start] > CurrentDate) ) RETURN PhaseEndDateBest Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
