Multiplying measure between tables

[vgdits]

Hi,

after several failed attempts or very unhandy solutions I would love to ask for advise.

I am trying to use PBI to revaluate stock.

Having product table with currency,

transactions table with product, qty, and date

exhc.rate table with currency, all dates within month until last available date within month (weekends will have no rates)

quote table with product, dates withing months until last available date (DIFFERENT from last exch. rate date - quotes can be either before or after last exch. rate)

I need to cumulate qty in stock, multiply the qty with rate from last date of the month and by quote from last date.

and in addition to this, SUM correctly within matrix table the stock value.

I am able to have measure to have correct qty cumul, incl. in periods where is no movement, but as soon as I make measure multiplying this cumul qty by rate and price returns correct row value but total line is wrong...

Any suggestions on which path I should take?

a) Need to use better measures

b) Running totals already in PowerQuery for each year_month and later measures

c) Everything in PowerQuery - merges...

Excel

Greetings from Liberec!

[v-juanli-msft]

Hi @vgdits

The matrix in your screenshot is your expected result or actual result?

What have you done in your pbix, could show me a screenshot to know which columns or measures you add in "row","column" and "value" field of the matrix?

the YM column should show year/month from 2001/1 to 2001/12 (12 months), right?

Based on my testing, you may need to cretae a new table with power query or a new calculated table with DAX, since adding a new calculated table to the model may slow the performance, please let know how large is your real dataset so i can choose a better way to slove this problem.

Best Regards

Maggie