Forum Discussion
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 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.
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.
4 Replies
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.
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.
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.
