Forum Discussion

myou's avatar
myou
Helper II
9 years ago
Solved

How to write a calculated column

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

 

 

Need Help
Show and Tell
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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.:smileyhappy:

    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

1 Reply

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    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.:smileyhappy:

    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