Inventory valuation

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