FIFO Quantity by Transfer Order

[alexricker0928]

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 #	Date	ItemId	Transfer Qty
1000-000071	10/5/2020	43810	2
1000-000072	10/5/2020	43810	1
1000-000074	10/5/2020	43810	1
1000-000076	10/5/2020	43810	1
1000-002647	10/5/2020	43810	-2
1000-000023	10/12/2020	23462	4
1000-000024	10/13/2020	23462	6
1000-000025	10/16/2020	23462	-5

The expected results would be:

Transfer/Sales Order #	Date	ItemId	FIFO
1000-000072	10/5/2020	43810	1
1000-000074	10/5/2020	43810	1
1000-000076	10/5/2020	43810	1
1000-000024	10/13/2020	23462	5

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 · Accepted solution]

@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]

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 Id	ItemId	Date	TransferQty
1000-000152	43810	8/1/2020	10
1000-007767	43810	8/5/2020	-3
1000-000153	43810	8/8/2020	10
1000-007768	43810	8/10/2020	-5
1000-000076	43810	10/5/2020	1
1000-000071	43810	10/5/2020	2
1000-002647	43810	10/5/2020	-2
1000-000072	43810	10/5/2020	1
1000-000074	43810	10/5/2020	1
1000-007767	43810	10/20/2020	3
1000-007767	43810	10/20/2020	-3

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

Transaction Id	ItemId	Date	FIFO
1000-000153	43810	8/8/2020	7
1000-000076	43810	10/5/2020	1
1000-000071	43810	10/5/2020	2
1000-000072	43810	10/5/2020	1
1000-000074	43810	10/5/2020	1
1000-007767	43810	10/20/2020	3

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

Transaction Id	ItemId	Date	FIFO
1000-000153	43810	8/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]

@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