Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
myou
Helper II
Helper II

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

 

 

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

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.Smiley Happy

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
    )
)

r2.PNG

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

r3.PNG

 

Regards

View solution in original post

1 REPLY 1
v-ljerr-msft
Employee
Employee

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.Smiley Happy

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
    )
)

r2.PNG

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

r3.PNG

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.