Find value based on previous rows and other column

[TK12345]

Hi guys,

I need help with the following:

I need to create the column: expected outcome enddate

This column needs to be based on the column cat. The expected outcome of the first row of each Cat needs to be empty. As well as the first row of each new type. The expected outcome enddate previous type needs to be filled if the previous row has the same  cat and the same Type. One addition to that, it is possible that within 1 cat, the type changes but has been ended on a previous row above. So for example row 14 gives uit 27-12-2020, because the cat is still 4 and the type 4 has an enddate two rows above. 

I have tried with duplicate table add index and merge back. Did not work out great. 

Who could help me, you could also use startdate and id maybe. 

Cat	Enddate	Type	Expected outcome enddate previous type	Startdatum	id
1		Type 0		26-11-2019	137175
1	31-3-2022	Type 1		20-6-2020	153676
1		Type 1	31-3-2022	20-6-2020	153677
1	31-3-2022	Type 1		2-12-2020	165553
1		Type 2		1-4-2022	227028
1		Type 2		1-4-2022	227029
2	16-12-2027	Type 3		17-12-2022	241229
3	29-3-2020	Type 3		14-1-2019	114249
3	31-12-2020	Type 3	29-3-2020	30-3-2020	142010
3	1-5-2022	Type 3	31-12-2020	1-1-2021	164519
3	23-3-2025	Type 3	1-5-2022	2-5-2022	230784
4	27-12-2020	Type 4		1-1-2019	113336
4	26-12-2021	Type 5		2-1-2019	113337
4	27-12-2022	Type 4	27-12-2020	28-12-2020	167168
4	31-12-2021	Type 6		1-1-2021	176085
4	26-12-2026	Type 5	26-12-2021	27-12-2021	215129

[Anonymous · Accepted solution]

Hi @TK12345 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Add index column in Power Query Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZE9DsIwDIWvgjojJf5P78FWMbKzcnviNEkxlRBSh9T53vOzs20LLNelfrfX83HJ9QCQUBNmWP2HDEyW+3XnKFG9zYhD4EWHRxGE1HTyPzH7bQuY0Istk4oInWyd5zSliJax/EWtjcK9DXg0tMFTr9pUMGBXUEuL6wh2CBLwXBow8sFXr328IKgz5zkeV2WeAklxG93fa20xygIhDvmVfOKy766lp2yFG859rRbS8LTv6YlIA6+jdeOl8Rh4O/tj8K/VcrymgZZPBUHooF8Dm+Yi50QaEo2+rkAQ8Be7vwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cat = _t, Enddate = _t, Type = _t, Startdatum = _t, id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cat", Int64.Type}, {"Enddate", type date}, {"Type", type text}, {"Startdatum", type text}, {"id", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Cat", Order.Ascending}, {"Type", Order.Ascending}, {"id", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Cat", "Type"},  {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Enddate", "Startdatum", "id", "Index"}, {"Enddate", "Startdatum", "id", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Index",{{"Index", Int64.Type}, {"Startdatum", type date}, {"Enddate", type date}, {"id", Int64.Type}})
in
    #"Changed Type1"

2. Create a calculated column as below to get the expected end date

Outcome enddate previous type = 
CALCULATE (
    MAX ( 'Table'[Enddate] ),
    FILTER (
        'Table',
        'Table'[Cat] = EARLIER ( 'Table'[Cat] )
            && 'Table'[Type] = EARLIER ( 'Table'[Type] )
            && 'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
    )
)

Best Regards