How to write nested if statement based on condition from other table

Question

&nbsp;I have two tables&nbsp;OppTableOpp idstatecodenameActual close date1lost5-7-20162open5-9-20153won10-5-2016&nbsp;updatesstagechange TableCustom_opportunityidstagecreatedonnextstagestartdateOppstagerank1prospect1-1-20162-1-201611qualify2-1-20165-7-2016(Actual close date from Opptable)22prospect3-1-20164-1-201612qualify4-1-20165-1-201622develop5-1-2016NOW DAte33Prospect6-1-20167-1-201613Qualify7-1-20168-1-201623develop8-1-201610-5-2016(Actual close date from Opptable)3&nbsp;How to do it , if I want to make a condition if opportunity is lost or won, then the &nbsp;next blank record in &nbsp;updatesstagechange Table will take the actual close date from OppTable, else if open it takes today date&nbsp;I wrote this column but its putting today date for all close, open and lost&nbsp;NextStageStart date = IF(ISBLANK(LOOKUPVALUE(updatesstagechange[createdon],Updatesstagechange[Custom_opportunityid],Updatesstagechange[Custom_opportunityid],Updatesstagechange[OppStageRank],Updatesstagechange[OppStageRank]+1)),NOW(),LOOKUPVALUE(Updatesstagechange[createdon],Updatesstagechange[Custom_opportunityid],Updatesstagechange[Custom_opportunityid],Updatesstagechange[OppStageRank],Updatesstagechange[OppStageRank]+1))&nbsp;OppStageRank = COUNTROWS( FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; updatesstagechange,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;updatesstagechange[custom_opportunityid]&lt;&gt;""&amp;&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; updatesstagechange[custom_opportunityid]=EARLIER(updatesstagechange[custom_opportunityid])&amp;&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; updatesstagechange[createdon]&lt;EARLIER(updatesstagechange[createdon])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )+1

v-sihou-msft · Answer

myou
&nbsp;
In&nbsp;your expression:
&nbsp;
LOOKUPVALUE(updatesstagechange[createdon],Updatesstagechange[Custom_opportunityid],Updatesstagechange[Custom_opportunityid],Updatesstagechange[OppStageRank],Updatesstagechange[OppStageRank]+1)
The current row "Updatesstagechange[Custom_opportunityid]" can never equal to "Updatesstagechange[Custom_opportunityid]+1". So your "NextStageStart date" will always return&nbsp;NOW().
&nbsp;
&nbsp;
Based on your description, you want to show different date based on Opp ID.&nbsp;Your expression should be&nbsp;like below:
&nbsp;
NextStageStart date =
IF (
    updatesstagechange[Custom_opportunityid] = 2,
    NOW (),
    LOOKUPVALUE (
        Opp[Actual close date],
        Opp[Opp id], updatesstagechange[Custom_opportunityid]
    )
)
Regards,
&nbsp;
&nbsp;
&nbsp;

Forum Discussion

How to write nested if statement based on condition from other table

1 Reply

Related content

Nested IF AND Statement/Conditional Column

Conditional Nested IF Statement

Power Query Nested IF AND Statement

Nested IF/AND Statement

Multiple nested IF statements

Opp id	statecodename	Actual close date
1	lost	5-7-2016
2	open	5-9-2015
3	won	10-5-2016

Custom_opportunityid	stage	createdon	nextstagestartdate	Oppstagerank
1	prospect	1-1-2016	2-1-2016	1
1	qualify	2-1-2016	5-7-2016(Actual close date from Opptable)	2
2	prospect	3-1-2016	4-1-2016	1
2	qualify	4-1-2016	5-1-2016	2
2	develop	5-1-2016	NOW DAte	3
3	Prospect	6-1-2016	7-1-2016	1
3	Qualify	7-1-2016	8-1-2016	2
3	develop	8-1-2016	10-5-2016(Actual close date from Opptable)	3