Forum Discussion
Calculating MTM Value based on Month End Position with Last Price
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
EdwardNg , Please find the attached solution after the signature.
Create common dimensions, Joins, and few measures
5 Replies
- amitchandak
Super User
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
