How to write a calculated column

[myou]

I am new to Power Bi and DAX, so I am trying  to write a calculated column to get the results of end date column in Table2. As an initial attempt, I created the below calculated columns in Table 2. The below calculated column dont take into consideration the status of an opportunity, like it puts now for all opportunities.. Am not getting the bold records in Table 2 as required

End date = IF(ISBLANK(LOOKUPVALUE(Table2[createdon],Table2[Id],Table2[Id],Table2[Rank],
Table2[Rank]+1))
,NOW()
,LOOKUPVALUE(Table2[createdon],Table2[Id],Table2[Id],Table2[Rank],Table2[Rank]+1))

 Rank = COUNTROWS( FILTER(  
                             Table2,                                                                                
                             Table2[id]<>""&&                                                                        
                            Table2[id]=EARLIER(Table2[id])&&                                                                
                            Table2[createdon]<EARLIER(Table2[createdon])                                                          )                                                  )+1 

OppTable

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

Table2 Table

Id	stage	createdon	enddate	Rank
1	prospect	1-1-2016	2-1-2016	1
1	qualify	2-1-2016	5-7-2016( 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( close date from Opptable)	3

[v-ljerr-msft · Accepted solution]

Hi @myou,

In this scenario, you should be able to use RELATED function to  a related value from another table. The formula below is for your reference.

End date = 
IF (
    ISBLANK (
        LOOKUPVALUE (
            Table2[createdon],
            Table2[Id], Table2[Id],
            Table2[Rank], Table2[Rank] + 1
        )
    ),
    IF (
        RELATED ( OppTable[status] ) = "lost"
            || RELATED ( OppTable[status] ) = "won",
        RELATED ( OppTable[close date] ),
        NOW ()
    ),
    LOOKUPVALUE (
        Table2[createdon],
        Table2[Id], Table2[Id],
        Table2[Rank], Table2[Rank] + 1
    )
)

Note: Make sure you have created the relationship for the two tables with the Id column like below.

Regards