Forum Discussion
Select Start Price from Earliest Date
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!
Try this Calculated Column using DAX
Start Price = CALCULATE ( FIRSTNONBLANK ( TableName[Price], 1 ), FILTER ( ALLEXCEPT ( TableName, TableName[Fund Name] ), TableName[Index] = 1 ) )
4 Replies
Try this Calculated Column using DAX
Start Price = CALCULATE ( FIRSTNONBLANK ( TableName[Price], 1 ), FILTER ( ALLEXCEPT ( TableName, TableName[Fund Name] ), TableName[Index] = 1 ) )Thank you so much Zubair_Muhammad
May I ask you to explain the formula, new to Power BI so learning the ropes
Thanks again!
HI KM007
Here goes my effort to explain
I am not very good at it :smileytongue: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
