cancel
Showing results for
Did you mean:
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

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

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

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors