Calculating MTM Value based on Month End Position with Last Price

[EdwardNg]

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

[amitchandak · Accepted solution]

@EdwardNg , Please find the attached solution after the signature.

Create common dimensions, Joins, and few measures

[EdwardNg]

for the qty, can we do live-to-date balance instead of just the balances for the month?

[EdwardNg]

@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

[amitchandak]

@EdwardNg , one product has two description ??

[EdwardNg]

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.