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.

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

Hi Anonymous 

Many to Many in your case is not a problem but if you have gaps in dates in both tables then most probably you're in trouble. 
Better to connect both through a Date table. If this the only requirement then you can generate a simple calendar table (New Table) using this code:

Date =
VAR MinDate = MIN ( Table1[Date] , Table2[Date] )

VAR MaxDate = MAX ( Table1[Date] , Table2[Date] )

VAR Result = CALENDAR ( MinDate, MaxDate)

RETRUN

Result

 

Then connect both tables to Date table one to many relationships. The rest depend on what visuals you need. 

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.