Forum Discussion

JaviGoldstein's avatar
JaviGoldstein
New Member
2 years ago
Solved

FIFO question

Hi, I am trying to "assign" pending orders to batches based on FIFO.   I have 2 tables:

 

tblSKU

tblPendingOrders

I want to add a column to tblSKU showing pending orders.   So, with the current pending orders, tblSKU would look like this:  

 

Looking for the best way to accomplish this with DAX.     Thx!

 

Need help with DAX
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi JaviGoldstein ,
    Here us my test data:

    Create a calculate column

    Pending Orders = 
VAR tblPO = 
    CALCULATE(
        MAX(tblPendingOrders[Amount]),
        FILTER(
            tblPendingOrders,
            tblPendingOrders[SKU] = tblSKU[SKU]
        )
    )
VAR temp_result = 
    IF(
        (tblPO-tblSKU[Balance]) >= 0,
        tblPO-tblSKU[Balance],
        0
    )
RETURN
IF(
    (temp_result - tblSKU[Balance]) > 0,
    temp_result - tblSKU[Balance],
    IF(
        tblPO > tblSKU[Balance],
        IF(
            tblSKU[Batch] = 1,
            tblSKU[Balance],
            temp_result
        ),
        temp_result
    )
)

    Final output

     

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi JaviGoldstein ,
    Here us my test data:

    Create a calculate column

    Pending Orders = 
VAR tblPO = 
    CALCULATE(
        MAX(tblPendingOrders[Amount]),
        FILTER(
            tblPendingOrders,
            tblPendingOrders[SKU] = tblSKU[SKU]
        )
    )
VAR temp_result = 
    IF(
        (tblPO-tblSKU[Balance]) >= 0,
        tblPO-tblSKU[Balance],
        0
    )
RETURN
IF(
    (temp_result - tblSKU[Balance]) > 0,
    temp_result - tblSKU[Balance],
    IF(
        tblPO > tblSKU[Balance],
        IF(
            tblSKU[Batch] = 1,
            tblSKU[Balance],
            temp_result
        ),
        temp_result
    )
)

    Final output

     

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Please use a proper logistics planning tool.  Power BI has no memory and is incapable of handling material allocation tasks. Its focus is on reporting data.

    • JaviGoldstein's avatar
      JaviGoldstein
      New Member
      2 years ago

      not using this as a logistics solution.   Reporting only.