Inventory valuation

[nileshp]

Hi,

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

there are 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

[Phil_Seamark]

HI @nileshp

Do you have some sample data showing your two tables (inventory and purchase)?

[nileshp]

simple model with test data added

https://www.dropbox.com/s/yd2bvhha0srpd6k/stock%20value.pbix?dl=0

Nilesh

[v-jiascu-msft · Accepted solution]

Hi Nilesh,

Please check out the demo in the attachment.

1. I'm afraid we need to modify the relationship first. It's the items that have balance QTY rather than the dates.

2. We can allocate the QTY first. So we add a calculated column like this.

allocation =
VAR currentItem = [Item Name]
VAR currentDate = [Date]
VAR maxDate =
    RELATED ( Stock[Date] )
VAR totalQty =
    RELATED ( Stock[Qty] )
VAR cumulation =
    CALCULATE (
        SUM ( PurReg[Qty] ),
        FILTER (
            ALLEXCEPT ( PurReg, PurReg[Item Name] ),
            'PurReg'[Date] >= currentDate
                && PurReg[Date] <= maxDate
        )
    )
RETURN
    IF (
        ISBLANK ( cumulation ),
        0,
        IF (
            cumulation <= totalQty,
            [Qty],
            IF ( cumulation - [Qty] < totalQty, totalQty - ( cumulation - [Qty] ), 0 )
        )
    )

3. Finally, the measure could be simple.

Measure =
SUMX ( 'PurReg', 'PurReg'[allocation] * 'PurReg'[Rate] )

Best Regards,
Dale

[nileshp]

Thanks. Sorry for the late reply. 

This solution works. still trying to understand the formula.

However, in actual model, stock table will have qty balance as on difference dates.. Say A is 1000 as on 31-Mar. If I add row A is 400 on 28-Feb, it will not work. I need to create a separate item table since item name is repeated in stock table.

No idea what to do after that. i think calcuated column may not work.

thanks

nilesh