cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft 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.

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

Microsoft 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.

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.