Select Start Price from Earliest Date

[KM007]

Hi

Please see attached photo.  

Create new column containing Start Price for each Fund

I want to create a new Column that contains the Start Price for each of the Funds

Please may you let me know how to do this

Thanks!

[Zubair_Muhammad · Accepted solution]

 @KM007

Try this Calculated Column using DAX

Start Price =
CALCULATE (
    FIRSTNONBLANK ( TableName[Price], 1 ),
    FILTER ( ALLEXCEPT ( TableName, TableName[Fund Name] ), TableName[Index] = 1 )
)

[KM007]

Thank you so much @Zubair_Muhammad

May I ask you to explain the formula, new to Power BI so learning the ropes

Thanks again!

[Zubair_Muhammad]

HI @KM007

Here goes my effort to explain
I am not very good at it

1) Since this is calculated column.... calculation is done on a ROW by ROW basis.
2) The second argument of Calculate i.e.

FILTER ( ALLEXCEPT ( TableName, TableName[Fund Name] ), TableName[Index] = 1 )

is used to narrow the scope of computation. This returns a Table with a single row i.e. the Table row with the same Fund Name and where Index =1

3) Finallly the first argument of Calculate returns that PRICE VALUE for that single row

FIRSTNONBLANK ( TableName[Price], 1 )

You can alternatively use

VALUES( TableName[Price])

as well

FirstNonBlank helps pick a single value in case  Filter function returns more than one ROW

[KM007]

Brilliantly explained - thanks!