Forum Discussion

alexricker0928's avatar
alexricker0928
Frequent Visitor
6 years ago
Solved

FIFO Quantity by Transfer Order

I have to calculate FIFO inventory quantities based on transfer order/itemid/date, only showing the transactions left after FIFO. The report will be run as of a specific date in the date filter, so t...
  • wdx223_Daniel's avatar
    wdx223_Daniel
    6 years ago

    alexricker0928 you can try this code, it might help you.

    FIFO :=
VAR _QTY =
    CALCULATE ( SUM ( Data[Transfer Qty] ) )
VAR _CurrentDate =
    MAX ( Data[Date] )
VAR _CurrentSO =
    MAX ( Data[Transfer/Sales Order #] )
VAR _CurrentItem =
    VALUES ( Data[ItemId] )
VAR _CurrentBalance =
    CALCULATE (
        SUM ( Data[Transfer Qty] ),
        FILTER (
            ALL ( Data ),
            Data[ItemId] IN _CurrentItem
                && ( Data[Transfer/Sales Order #] <= _CurrentSO
                && Data[Date] <= _CurrentDate
                || Data[Transfer Qty] < 0 )
        )
    )
RETURN
    IF ( _CurrentBalance > 0 && _QTY > 0, MIN ( _CurrentBalance, _QTY ) )

     

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
6 years ago

alexricker0928 you can try this code, it might help you.

FIFO :=
VAR _QTY =
    CALCULATE ( SUM ( Data[Transfer Qty] ) )
VAR _CurrentDate =
    MAX ( Data[Date] )
VAR _CurrentSO =
    MAX ( Data[Transfer/Sales Order #] )
VAR _CurrentItem =
    VALUES ( Data[ItemId] )
VAR _CurrentBalance =
    CALCULATE (
        SUM ( Data[Transfer Qty] ),
        FILTER (
            ALL ( Data ),
            Data[ItemId] IN _CurrentItem
                && ( Data[Transfer/Sales Order #] <= _CurrentSO
                && Data[Date] <= _CurrentDate
                || Data[Transfer Qty] < 0 )
        )
    )
RETURN
    IF ( _CurrentBalance > 0 && _QTY > 0, MIN ( _CurrentBalance, _QTY ) )

 

alexricker0928's avatar
alexricker0928
Frequent Visitor
6 years ago

Hi wdx223_Daniel,

 

The solution you provided worked for the original data. Thank you for your response!

 

However, with additional data coming in, it doesn't work when the in and out qtys dont match up exactly like they did in the data I provided first. For example:

 

This data:

Transaction IdItemIdDateTransferQty
1000-000152438108/1/202010
1000-007767438108/5/2020-3
1000-000153438108/8/202010
1000-007768438108/10/2020-5
1000-0000764381010/5/20201
1000-0000714381010/5/20202
1000-0026474381010/5/2020-2
1000-0000724381010/5/20201
1000-0000744381010/5/20201
1000-0077674381010/20/20203
1000-0077674381010/20/2020-3

 

I would expect to see this in the PBI table using FIFO logic:

Transaction IdItemIdDateFIFO
1000-000153438108/8/20207
1000-0000764381010/5/20201
1000-0000714381010/5/20202
1000-0000724381010/5/20201
1000-0000744381010/5/20201
1000-0077674381010/20/20203

 

This is what shows currently in the PBI table given your original solution:

Transaction IdItemIdDateFIFO
1000-000153438108/8/2020

7

 

Which is technically correct, but I also want to see any transactions and their quantities after that, that have not been affected by negative quantities.

 

Do you have any idea what needs to be added to your original code to help solve the problem?

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    6 years ago

    alexricker0928  i think the basic assumption is Transfer/Sales Order is a series of numbers which is cumulatively growing, not random. so if you change the sample data like that, the measure get a right result