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

[Taffalaffa]

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 unique within the project but not unique otherwise; Column C has the actual start date of the activity in column B; Column D has the predecessor Activity ID (unique within the project but not unique otherwise).  What I am trying to do is compare Column D (Predecessor Activity ID) to Column B (Activity ID) within the same project (Column A) and return the Actual start date of the Predecessor Activity in Column E.... I cannot figure of the proper DAX expression and everytime I try it tells me it can't retun a date based on text columns. This is the result I am looking for but in PBI....  Any help would be really appreciated!

[Taffalaffa]

@Jihwan_Kim thank you so much for helping me! @Jihwan_Kim @Fowmy @amitchandak @PaulOlding I am new to power BI and I really appreciate this.  I don't think I explained my issue well... I have one table that has all the information I need.  It has all the activities from all my projects.  It shows the predecessors to each activity.  What I need is to add a cloumn (Pred act_start_date) to fill in the predecessor actual finish date (if it has one).  I was hoping that within the same PROJ_ID, I could lookup the Predecessor_Task_Code value in the  the Task_Code column and then return the value of the TASK.act_start_date but I can't seem to figure out how to do it.  When I try the LookupValue option it says it can't return a date value based on text strings.   (Note that task code is unique within the PROJ_ID but can be resused amongst other PROJ_ID's.  Also every Predecessor_Task_Code exists in the TASK_CODE column).  My end goal is that I need to be able to compare the Predecessor Actual Start date (Pred act_start_date) to its Successor Actual Start Date (TASK.act_start-date).

[Jihwan_Kim]

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]

),

""

)

https://www.dropbox.com/s/ic374jdanlhxi9i/taffa.pbix?dl=0