Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have 2 tables, Price and Transactions. Example as below:
Price Table
Date | Product | Price |
2/1/2020 | A | 1.5 |
29/1/2020 | A | 1.3 |
2/2/2020 | A | 1.25 |
28/2/2020 | A | 1.2 |
2/1/2020 | B | 5.1 |
29/1/2020 | B | 5.3 |
2/2/2020 | B | 5.4 |
28/2/2020 | B | 5.55 |
Transactions Table:
TradeDate | Product | Qty |
5/1/2020 | A | 100 |
6/1/2020 | A | 300 |
7/1/2020 | A | 400 |
8/1/2020 | A | -200 |
7/2/2020 | A | -100 |
8/2/2020 | A | 250 |
5/1/2020 | B | 120 |
6/1/2020 | B | 210 |
7/1/2020 | B | 300 |
8/1/2020 | B | 200 |
7/2/2020 | B | -400 |
8/2/2020 | B | 100 |
I want to calcuate the month end market value based on the last price for the month and accumulated balance for each of the products.
Expected Results:
Month | Product | Qty | Last Price | MarketValue |
Jan | A | 600 | 1.3 | 780 |
Jan | B | 830 | 5.3 | 4399 |
Feb | A | 750 | 1.2 | 900 |
Feb | B | 530 | 5.55 | 2941.5 |
How do i link the tables and calculate the Market value by Month and Product?
Thanks
Ed
Solved! Go to Solution.
@EdwardNg , Please find the attached solution after the signature.
Create common dimensions, Joins, and few measures
@EdwardNg , Please find the attached solution after the signature.
Create common dimensions, Joins, and few measures
for the qty, can we do live-to-date balance instead of just the balances for the month?
@amitchandak thanks for the prompt response.
Also, if i have additional fields in the transactions i want to display in the summary, it gives me a problem. For example if we have product description, it gives the permuatation, as below:
Could you kindly advise? appreciate your thoughts on this.
Many thanks
@EdwardNg , one product has two description ??
each product only has 1 description. But this is probably just the simplified version. There could be other fields like company, etc. So each product could be have different values for company. hope this is clear.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |