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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Case Number[/TD]
[TD]Change ID[/TD]
[TD]Change Date[/TD]
[TD]Old Status[/TD]
[TD]New Status[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]aaaaaaa[/TD]
[TD]01/01/2018[/TD]
[TD]None[/TD]
[TD]Created[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]eeeeeee[/TD]
[TD]05/05/2018[/TD]
[TD]Created[/TD]
[TD]In Progress[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]gggggg[/TD]
[TD]07/07/2018[/TD]
[TD]In Progress[/TD]
[TD]On Hold[/TD]
[/TR]
</tbody>[/TABLE]



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...