Find next start date in table

[asodie]

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!

[vicky_]

To get a new column for the next phase time:

next start = CALCULATE(MIN('Table'[Start]), REMOVEFILTERS('Table'[Start]), 'Table'[Start] > EARLIER('Table'[Start]))

[asodie]

Hi,

I get a circular dependency error when I tried this! Any ideas?

[v-jingzhan-msft]

Thanks @vicky_ for your concern about this thread.

@asodie 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 PhaseEndDate

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[asodie]

This worked great on my dummy data (thank you!) but when I do it on my real, much larger dataset I get this error:

There's not enough memory to complete this operation. Please try again later when there may be more memory available.

Any ideas how to make it more memory efficient?

[v-jingzhan-msft]

Hi @asodie 

Not sure if this would be more efficient, you may give it a try:

phase end date = 
VAR CurrentPhase = 'Table'[Phase]
RETURN
CALCULATE(MIN('Table'[Phase Start]),ALLEXCEPT('Table','Table'[ID]),'Table'[Phase]>CurrentPhase)

Best Regards,
Jing