Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Connect two datasets and retrieve last value

Hello everyone, I'm doing a dashboard for my investments and I have some doubts on how to relate two tables.   One has all the movements I have done in my online broker and the other has 3 colum...
Model
relationships
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Anonymous 

    It's possible to get the latest value in another table. According to the Product name of the current row, go to another table to find the value of the latest date of the same product name.

    e.g. create the the measure

    get the latest close_price = 
VAR _latestDate =
    CALCULATE (
        MAX ( Table2[Date] ),
        FILTER ( ALL ( Table2 ), Table2[Ticker] = MIN ( Table1[Product] ) )
    )
RETURN
    CALCULATE (
        MAX ( Table2[close_price] ),
        FILTER (
            ALL ( Table2 ),
            Table2[Date] = _latestDate
                && Table2[Ticker] = MIN ( Table1[Product] )
        )
    )

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I do have gaps between tables, but I also have a Calendar Table already created.

At least, I would like a visual to see the global and daily variation of each stock/etf

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

I don't realy get it. If you don't have gaps in the dates in the dimension table, then whay should look for the nearest date while you have the exact one? Are you sure you don't have missing dates in the dimension table? 
The date table that you need to create in power query is just one column table that will become part of the dimension table after joining both tables. But this column shall not have missing dates.