BOM order stock requirements (with priority)

DAX DAX Calculations
19

Hi @Wlknsn,

First as a reference I’ve created a summary Test table in Power Query.
You will find that the Qty doesn’t match the provided expected outcome. this is due to the fact that you are summing both Sales Qty and BOM Qty on an Sales Order level before multiplication.

image
image689×501 7.95 KB

.
To have each BOM item qty multiplied by the Sales qty before summation I’ve created this measure:

BOM Item Quantity by Order Rank = 
VAR vTable = 
    ADDCOLUMNS(
        FILTER(
            GENERATEALL( 'Order Lines', 'Product Boms' ),
            [Product] = [Product Bom]
        ),
        "@Rank", [Order Rank Expected Delivery],
        "@Value", [Orderline Qty] * [Bom Qty]
    )
RETURN

SUMX( vTable, [@Value] )

.
Next I reused the cumulative pattern by @Greg

Cum Total BOM Quantity by Order Rank = 
VAR __CurrentOrderRank = [Order Rank Expected Delivery]
VAR __CurrentItem = SELECTEDVALUE('Bom Items'[Bom Item])
RETURN
CALCULATE(
    [BOM Item Quantity by Order Rank],
    FILTER(
        ALL(Orders),
        [Order Rank Expected Delivery] <= __CurrentOrderRank
        )
)

.
With this result.

image
image569×522 9.54 KB

Here’s your sample file. edna - Bom Stock Report.pbix (55.5 KB)
I hope this is helpful.

2 Likes