inventory valuation

[nileshp]

Hi,

I am not able to figure out a measure for the follwing problem.

there ae 2 tables, inventory balance showing item wise inventory balance qty as on say, 31-Mar-2018. Second table is purchase register which has column of item name, purchase date, qty, rate and amount.

I want a measure to calculate value of inventory balance by applying the rate of latest purchase. if X item has balance of 100 and in purchase register X if purchased as follows:

Date,Qty,Rate,Amt

1-Jan-2018,50,100,5000

2-Feb-2018,70,110,7700

5-Mar-2018,40,90,3600

31-Mar-2018,50,80,4000

then value of 100 units of X will be 8700 (50x80+40x90+10x110)

Pl help

Nilesh

[Greg_Deckler]

So, do you always take the last 3 purchases or let's say the last 2 lines didn't exist, would it be 100*110? What if the purchases add up to over 100? Like the last line is 130 @ 80? This question leaves more questions than answers.  Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490