Forum Discussion

alexricker0928's avatar
alexricker0928
Frequent Visitor
5 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 the calculations should be dynamic based on the date. 
 
For example, the data:
 
Transfer/Sales Order #DateItemIdTransfer Qty
1000-00007110/5/2020438102
1000-00007210/5/2020438101
1000-00007410/5/2020438101
1000-00007610/5/2020438101
1000-00264710/5/202043810-2
1000-00002310/12/2020234624
1000-00002410/13/2020234626
1000-00002510/16/202023462-5
The expected results would be:
 
Transfer/Sales Order #DateItemIdFIFO
1000-00007210/5/2020438101
1000-00007410/5/2020438101
1000-00007610/5/2020438101
1000-00002410/13/2020234625

 

I tried following this article: https://radacad.com/dax-inventory-or-stock-valuation-using-fifo

but to no avail. Any help is appreciated!! 

 

Here is a link to my PBI document: https://drive.google.com/file/d/1aQesVczvSoCTrwuDTnnyliFBYV5Ye1yr/view?usp=sharing

  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 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 ) )

     

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    5 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
      5 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
        Community Champion
        5 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