Forum Discussion
Measure to relate data from 2 tables
Hello,
I hope someone could advice me in my case. I have a table with buy/sell of shares and another table with market prices.
I need to create a table (from source1 data), where I have share name, nr of pieces and latest market price, and a filter by months. I need the market price to change if I filter different months. So if I filter month 4, I need the market price for CEZ to show value 503 etc. Can someone advise me with formula for the market price measure?
thank you!
Source 1:
| Date | Month | Share | Pieces |
| 01.03.2023 | 3 | CEZ AS | 300 |
| 06.05.2023 | 5 | ANHEUSER | 400 |
| 08.09.2023 | 9 | BAYER AG-REG | 200 |
Source 2:
| Share | Month | Market price |
| CEZ AS | 1 | 500 |
| CEZ AS | 2 | 501 |
| CEZ AS | 3 | 502 |
| CEZ AS | 4 | 503 |
| CEZ AS | 5 | 504 |
| CEZ AS | 6 | 505 |
| CEZ AS | 7 | 506 |
| CEZ AS | 8 | 507 |
| CEZ AS | 9 | 508 |
| CEZ AS | 10 | 509 |
| CEZ AS | 11 | 510 |
| CEZ AS | 12 | 511 |
| ANHEUSER | 1 | 34,5 |
| ANHEUSER | 2 | 34,6 |
| ANHEUSER | 3 | 34,7 |
| ANHEUSER | 4 | 34,8 |
| ANHEUSER | 5 | 34,9 |
| ANHEUSER | 6 | 35,0 |
| ANHEUSER | 7 | 35,1 |
| ANHEUSER | 8 | 35,2 |
| ANHEUSER | 9 | 35,3 |
| ANHEUSER | 10 | 35,4 |
| ANHEUSER | 11 | 35,5 |
| ANHEUSER | 12 | 35,6 |
| BAYER AG-REG | 1 | 21,0 |
| BAYER AG-REG | 2 | 22,0 |
| BAYER AG-REG | 3 | 23,0 |
| BAYER AG-REG | 4 | 24,0 |
| BAYER AG-REG | 5 | 25,0 |
| BAYER AG-REG | 6 | 26,0 |
| BAYER AG-REG | 7 | 27,0 |
| BAYER AG-REG | 8 | 28,0 |
| BAYER AG-REG | 9 | 29,0 |
| BAYER AG-REG | 10 | 30,0 |
| BAYER AG-REG | 11 | 31,0 |
| BAYER AG-REG | 12 | 32,0 |
5 Replies
- saud968
Memorable Member
To achieve this, you can create a measure in your Source 1 table that looks up the latest market price for each share based on the selected month. You can use the following DAX (Data Analysis Expressions) formula in Power BI or Excel (assuming you have a relationship between Source 1 and Source 2 tables on the "Share" and "Month" columns):
Market Price =
VAR SelectedMonth = SELECTEDVALUE('Source 1'[Month])
RETURN
CALCULATE(
MAX('Source 2'[Market price]),
FILTER(
'Source 2',
'Source 2'[Share] = 'Source 1'[Share] &&
'Source 2'[Month] = SelectedMonth
)
)
This measure uses the CALCULATE function to evaluate the maximum (latest) market price for the selected share and month. The FILTER function filters the Source 2 table based on the selected share and month in Source 1.Remember to replace 'Source 1' and 'Source 2' with your actual table names. Once you create this measure, you can use it in your Source 1 table, and it should dynamically update the market price based on the selected month.
Note: Ensure that there is a relationship between the "Share" columns in both tables for this measure to work correctly. If there isn't one, you may need to create a relationship between the "Share" columns in Source 1 and Source 2 tables.
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos! Hello,
the formula gives me error (the value that gives error is the name of the share).
Also I should mention that i work with Calendar(date) and the "month" filter is based on "Calendar(month) value. Also it gets more complicated, as when I do not filter any month, I need the last known value to be shown.
Also the relationship between share from 2 tables is problematic, since it is m:n relationship
If it helps with the formula, It does not need to look for max value of each month, because the data source is already altered to show only 1 price per month per share.
Thank you
