Check if a contract has a future contract associated to it

[nabu]

Hello, I have a file with contracts that are categorized by status as either expired, active or future based on the valid from and valid to dates. I want to add a Renewal Status column that checks unique contract numbers and lets me know that contract number has a future contract associated with it or not like the output example below.

Thank you in advance

Input

Output Column is Renewal Status column

[vapid128 · Accepted solution]

emmmmmmmm.

add colnum

index = 

switch(true(),

[Status]="Expired",1,

[Status]="Avtive",2,

[Status]="Furture",3,

0

)

add colnum

Renewal Status= 

If(calculate(max(index),allexpert(table, table[Contract #])=3,

"Future contract exists",

"Futrue contract does not exist"

)

[nabu]

Thank you, that worked!!