Forum Discussion

nileshp's avatar
nileshp
Regular Visitor
8 years ago
Solved

Inventory valuation

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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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.

    Inventory_valuation

    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

     

4 Replies

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        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.

        Inventory_valuation

        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