Forum Discussion

benalfassy's avatar
benalfassy
Regular Visitor
8 years ago
Solved

How to create calculated column based on different rows

Hi,

 

I want to create new column with value that will be set like this:

 

i have ID, type, Date columns

 

If my type is "A"

and there is row where its ID equals to mine 

and its date bigger then my 

and its type is "B"

then value = "C"

else  value = "D"

 

Thanks !

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi benalfassy

     

    Try this

     

    =
IF (
    Table1[Type] = "A"
        && COUNTROWS (
            FILTER (
                Table1,
                Table1[ID] = EARLIER ( Table1[ID] )
                    && Table1[Type] = "B"
                    && Table1[Date] > EARLIER ( Table1[Date] )
            )
        )
            > 0,
    "C",
    "D"
)

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi benalfassy

     

    Try this

     

    =
IF (
    Table1[Type] = "A"
        && COUNTROWS (
            FILTER (
                Table1,
                Table1[ID] = EARLIER ( Table1[ID] )
                    && Table1[Type] = "B"
                    && Table1[Date] > EARLIER ( Table1[Date] )
            )
        )
            > 0,
    "C",
    "D"
)