Forum Discussion

Taffalaffa's avatar
Taffalaffa
Icon for Helper I rankHelper I
5 years ago

LOOKUPVALUE - Compare Text Values in columns to get a date value

Hi... Jihwan_Kim  Fowmy amitchandak PaulOlding I am really hoping someone can help me.  I have a table in power BI.  Column A has the unique Project Number, Column B has an Activity ID. that is uniqu...
LOOKUPVALUE
VLOOKUP
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

 

Successor Activity ID : =
VAR currentproject =
MAX ( Projects[Project#] )
VAR currentrank =
RANKX (
FILTER ( ALL ( Data ), Data[Project#] = currentproject ),
CALCULATE ( MAX ( Data[Actual Start] ) ),
,
ASC
)
VAR _rankbydate =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( ALL ( Data ), Data[Project#] = currentproject ),
Projects[Project#],
Activities[ActivityID]
),
"@rankbydate",
CALCULATE (
RANKX (
FILTER ( ALL ( Data ), Data[Project#] = currentproject ),
CALCULATE ( MAX ( Data[Actual Start] ) ),
,
ASC
)
)
)
RETURN
COALESCE (
MAXX (
FILTER ( _rankbydate, [@rankbydate] = currentrank + 1 ),
Activities[ActivityID]
),
""
)
 
Successor Actual Startdate : =
VAR currentproject =
MAX ( Projects[Project#] )
VAR currentrank =
RANKX (
FILTER ( ALL ( Data ), Data[Project#] = currentproject ),
CALCULATE ( MAX ( Data[Actual Start] ) ),
,
ASC
)
VAR _rankbydate =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( ALL ( Data ), Data[Project#] = currentproject ),
Projects[Project#],
Activities[ActivityID], Data[Actual Start]
),
"@rankbydate",
CALCULATE (
RANKX (
FILTER ( ALL ( Data ), Data[Project#] = currentproject ),
CALCULATE ( MAX ( Data[Actual Start] ) ),
,
ASC
)
)
)
RETURN
COALESCE (
MAXX (
FILTER ( _rankbydate, [@rankbydate] = currentrank + 1 ),
Data[Actual Start]
),
""
)