Connect two datasets and retrieve last value

[filipebodas]

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 columns (Date, Ticker, close_price)

TABLE_1:

Date	Type_ID	Type	Movement	Product	Cost	Quantity	Amount
24/03/2020	1	Expenditure	Purchase	IWDA	43.09	1	43.09
24/03/2020	1	Expenditure	Comission	IWDA	2	1	2
19/04/2020	1	Expenditure	Purchase	EMIM	22.06	2	43.12

etc...

TABLE_2:

Date	Ticker	close_price
15/02/2020	IWDA	74.41
15/02/2020	EMIM	31.38
14/02/2020	IWDA	74.55
14/02/2020	EMIM	31.5

etc...

I want to create a relationship, so I can get the latest close_price for each ticker. Is a M:M relation a problem? The final goal is to see the variation in value in each date for each ticker.

[v-xiaotang · Accepted solution]

Hi @filipebodas 

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]

Hi @filipebodas 

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. 

[filipebodas]

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]

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.