How do i refer the previous and next row and do the calculation

[kaarthirm]

How do i refer the previous and next row and do the calculation,

Staus_id = 2 = Start 

Staus_id  = 3 = End

I need a duration (End - Start ) by using ac_start_date.  I tried creating index and fillup  but it didn't work properly, when you notice the below table ,the status  is not always 2 and 3.   Appricate your advise and help.

[kaarthirm · Accepted solution]

Got it fixed, Thanks guys..

[Mahesh0016]

if #"Added Index"{[Index]+1} [#"Table (2).As_St_date"] = null & [#"Table (2).As_St_date"]<>null then #"Added Index"{[Index]+1} [As_St_date] else null

>>I hope this helps.

[kaarthirm]

Hi, Thanks for help, But date column never be null.  the Start  & End ( ie 2 & 3)  records aren't always next to each other in the table.  If you refer the above table , highlighted yellow. 

[jennratten]

Hello! @ImkeF has a great function for this.  

this is how you can add a column that returns the previous row or next row value for a column.  Screensnips are shown below for the steps and a sample script is provided.  Please let me know if you need help integrating this solution into your project.  

Add a column with the previous row value for another column - this one shows the previous row value of Column1. (Using Imke's custom function).  Once the new column is added the calculation can easily be done because the values are present in the same row.

let
    // Imke's function
    // https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/
    func =   
    (MyTable as table, MyColumnName as text) =>
    let
        Source = MyTable,
        ShiftedList = {null} &  List.RemoveLastN(Table.Column(Source, MyColumnName),1),
        Custom1 = Table.ToColumns(Source) & {ShiftedList},
        Custom2 = Table.FromColumns(Custom1, Table.ColumnNames(Source) & {"Previous Row"})
    in
        Custom2 ,
    documentation = [
    Documentation.Name =  " Table.PreviousRow ",
    Documentation.Description = " Superfast way to reference previous row ",
    Documentation.LongDescription = " Superfast way to reference previous row ",
    Documentation.Category = " Table ",
    Documentation.Source = " www.TheBIccountant.com .  http://tiny.cc/hhus5y . ",
    Documentation.Version = " 1.0 ",
    Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. http://tiny.cc/hhus5y . ",
    Documentation.Examples = {[Description =  "  ",
    Code = "  ",
    Result = "  "]}],      
    ReplaceMetadata = Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)),

    // Data 
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUYp0DQaSfvqOSrE60UruRampeVA+kPQHCwalpqALBZQWFeSkQvgQM0Cikak5OfnlqIbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Color = _t, Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Color", type text}, {"Column1", type text}, {"Column2", type text}}),

    // invoke function
    GetPreviousRow = func(#"Changed Type", "Column1")

in
     GetPreviousRow 

[kaarthirm]

Hi, Thanks for your reply, The column i'm trying to returns next is not always each other.  The Start  & End ( ie 2 & 3)  records aren't always next to each other in the table.  If you refer the above table , highlighted yellow.  

Imke's function works, But it's not 100%, see the result below. i don't why it's is pulling values only for some rows. I used  fnTable_ReferenceDifferentRow(#"Sorted Rows1", 1, {"ac_st_date"}, {"Stage_Status"}, "Next")