Determining the next historical row for a Support Case and getting one field from there

[Alice Morland]

Hi guys and girls,

Here's me hoping there's an easy way to produce this. Help will be enormously appreciated!

In short, this is the situation: I have one Support Case which went through different Stages on different Dates:

Case Number	Change ID	Change Date	Old Status	New Status
222	aaaaaaa	01/01/2018	None	Created
222	eeeeeee	05/05/2018	Created	In Progress
222	gggggg	07/07/2018	In Progress	On Hold

<tbody>
</tbody>

Goal: in the first row, (change ID aaaaaaa) I need to add the next change date (so change ID eeeeeeee, 05/05/2018).

Why?: because I need to learn how long did it stay in that status.

Problem: I have no idea how to determine which row that is.

Help a girl out

Alice

 

[Alice Morland]

Someone has provided a solution here: https://community.powerbi.com/t5/De...pport-Case/m-p/533439/highlight/false#M250118

 

[JazzSP8]

Pretty sure there will be a better solution to this out there, but in the absence of that so far...

If you next date is always going to be below the current row then you an use a VLOOKUP to look below the current row.

Assuming your table is Column A1- E4 then in F2 you could use;

=VLOOKUP(A2,A3:E4,3,FALSE)

And drag down to bring back the date for eeeeeee.

Not very eloquent but...