Forum Discussion

leolapa_br's avatar
leolapa_br
Resolver II
1 year ago
Solved

Get First Value From A Column

Hi have a table called "StockQuotes" that looks like this:

I wrote the following code as an attempt to fetch the first available (from the earliest date) Close Price for each respective stock ticker:

First Close = 
  VAR Ticker = SELECTEDVALUE( StockQuotes[Ticker] )
  VAR Result = MINX (
    TOPN (
        1,
        FILTER ( StockQuotes, StockQuotes[Ticker] = Ticker ),
        StockQuotes[Date], ASC
    ),
    StockQuotes[Close Price]
)
RETURN
  Result

I then created the matrix visual below with the dates along the rows and stock tickers across the top to check whether the above code produced the desired results:

But as it can be seen the code it's not working since I was expecting the matrix to return repeated numbers under the First Close column for each respective ticker (i.e.: $63.96 for AAPL, $89.57 for AMZN, $323,400.00 for BRK-A, $63.69 for GOOG and so on...), not a mirror image of the Close Price column.

 

What am I missing here?

 

Any help on getting to the right code is greatly apreciated!

  • ChatGTP came to the rescue here and produced the following code that solved the issue:

    First Close = 
    CALCULATE(
        FIRSTNONBLANK(StockQuotes[Close Price], 1),
        FILTER(
            ALL(StockQuotes),
            StockQuotes[Ticker] = SELECTEDVALUE(StockQuotes[Ticker]) &&
            StockQuotes[Date] = 
                CALCULATE(
                    MIN(StockQuotes[Date]),
                    ALLEXCEPT(StockQuotes, StockQuotes[Ticker])
                )
        )
    )

2 Replies

  • ChatGTP came to the rescue here and produced the following code that solved the issue:

    First Close = 
    CALCULATE(
        FIRSTNONBLANK(StockQuotes[Close Price], 1),
        FILTER(
            ALL(StockQuotes),
            StockQuotes[Ticker] = SELECTEDVALUE(StockQuotes[Ticker]) &&
            StockQuotes[Date] = 
                CALCULATE(
                    MIN(StockQuotes[Date]),
                    ALLEXCEPT(StockQuotes, StockQuotes[Ticker])
                )
        )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi leolapa_br ,

     

    Thanks for sharing, this will help other users who have the same question.

     

     

    Best regards,

    Mengmeng Li