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. 

[v-jianboli-msft]

Hi @TK12345 ,

Please try:

First, create an index column:

Please try:

Column =
VAR _a =
    MINX (
        FILTER (
            'Table',
            [Cat] = EARLIER ( 'Table'[Cat] )
                && [Type] = EARLIER ( 'Table'[Type] )
        ),
        [Index]
    )
VAR _b =
    MAXX (
        FILTER (
            'Table',
            [Cat] = EARLIER ( 'Table'[Cat] )
                && [Type] = EARLIER ( 'Table'[Type] )
                && [Index] < EARLIER ( 'Table'[Index] )
        ),
        [Index]
    )
RETURN
    IF (
        [Index] = _a,
        BLANK (),
        MAXX ( FILTER ( 'Table', [Index] = _b ), [Enddate] )
    )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TK12345]

Hi thanks for your reply,

For now it works, but is it possible to do this in the Power Query, because of the next steps I have to do.

[TK12345]

Hi thanks for your reply,

For now it works, but is it possible to do this in the Power Query, because of the next steps I have to do.