Forum Discussion

TK12345's avatar
TK12345
Icon for Resolver II rankResolver II
3 years ago

Find value based on previous rows and other column

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. 

 

 

Need Help
Show and Tell

3 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      TK12345
      Icon for Resolver II rankResolver II
      3 years ago

      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's avatar
      TK12345
      Icon for Resolver II rankResolver II
      3 years ago

      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.