Age FIFO Inventory

[alexricker0928]

My DAX skills are decent at best, and I've been struggling to figure this issue out for a while now. Any help would be GREATLY appreciated. 

Here is the context of the data:

Transfer orders move inventory into warehouse, and sales orders move inventory out of the warehouse. The report has a date filter, and the user should be able to "back date" the report using that filter. The goal is to age the inventory by transfer order on a FIFO basis. 

This is the example data:

Transfer Order/Sales Order	Date	ItemId	Transfer Qty
1000-000072	3/20/2020	040252	1
1000-000073	5/30/2020	040252	1
1000-000074	6/15/2020	040252	1
1000-001881	6/16/2020	040252	-1
1000-001886	9/11/2020	040252	-3
1000-000051	9/11/2020	043809	4
1000-000061	9/11/2020	040252	2
1000-000062	9/11/2020	040252	1
1000-000071	9/12/2020	040252	2

Transfer orders are characterized by positive quantities, and Sales orders are characterized by negative quantities. 

Let's say I want to run the report as of 6/17/2020. This is what I expect to see. These rows would be taken into account given the date filter. The sales order from 6/16 would be subtracted from the transfer order on 3/20 given FIFO. So a quantity of zero would be aged for that transfer order. The other transfer orders are then aged accordingly.

Transfer Order/Sales Order	Date	ItemId	Transfer Qty	FIFO	Days Aged
1000-000072	3/20/2020	040252	1	0	89
1000-000073	5/30/2020	040252	1	1	18
1000-000074	6/15/2020	040252	1	1	2
1000-001881	6/16/2020	040252	-1	THIS ROW WOULD DISAPPEAR SOMEHOW

Of course there's added complexity with more items/rows/when the sales order qty's don't exactly match the first transfer order qty. That should be taken into account. 

Here is a version of the report with data. 

https://drive.google.com/file/d/1jeASqPwou3ad4hGrS_vv288Ky1jnAc0d/view?usp=sharing

Thank you!!

[stevedep]

Hi @alexricker0928 ,

This one required some thought and the solution is not perfect, since filtering out that last row is very tricky (when trying to use a visual level filter), and I did not manage this. The other requirements should be in this solution though. 

The below code ranks the incoming items (for the same item id) and ranks the outgoing items (same item id). Fifo is set as such that any ingoing item might be set to 0 whenever the outgoing item matches the rank. Calculating the age is pretty straightforward. 

___FiFo = 
VAR _tbl =
    ADDCOLUMNS (
        ALLSELECTED ( 'Test Data' ),
        "R",
            IF (
                 ( 'Test Data'[Transfer Qty] ) < 0,
                RANKX (
                    FILTER (
                        ALLSELECTED ( 'Test Data' ),
                        'Test Data'[Transfer Qty] < 0
                            && [ItemId] = ( 'Test Data'[ItemId] )
                    ),
                    MIN ( 'Test Data'[Date] ),
                    ,
                    ASC,
                    DENSE
                ),
                RANKX (
                    FILTER (
                        ALLSELECTED ( 'Test Data' ),
                        'Test Data'[Transfer Qty] > 0
                            && [ItemId] = ( 'Test Data'[ItemId] )
                    ),
                    CALCULATE ( MIN ( 'Test Data'[Date] ) ),
                    ,
                    ASC,
                    DENSE
                )
            )
    )
VAR _rank =
    IF (
        SELECTEDVALUE ( 'Test Data'[Transfer Qty] ) < 0,
        RANKX (
            FILTER (
                ALLSELECTED ( 'Test Data' ),
                'Test Data'[Transfer Qty] < 0
                    && 'Test Data'[ItemId] = SELECTEDVALUE ( 'Test Data'[ItemId] )
            ),
            MIN ( 'Test Data'[Date] ),
            ,
            ASC,
            DENSE
        ),
        RANKX (
            FILTER (
                ALLSELECTED ( 'Test Data' ),
                'Test Data'[Transfer Qty] > 0
                    && 'Test Data'[ItemId] = SELECTEDVALUE ( 'Test Data'[ItemId] )
            ),
            CALCULATE ( MIN ( 'Test Data'[Date] ) ),
            ,
            ASC,
            DENSE
        )
    )
RETURN
    // _rank
    // CONCATENATEX(_tbl, [Date] & " r: " & [R] & "
    //  ")

    IF (
        SELECTEDVALUE ( 'Test Data'[Transfer Qty] ) < 0,
        BLANK(),
        IF (
            IF (
                SELECTEDVALUE ( 'Test Data'[Transfer Qty] ) > 0,
                COUNTROWS ( FILTER ( _tbl, [R] = _rank && [Transfer Qty] < 0 && 'Test Data'[ItemId] = SELECTEDVALUE('Test Data'[ItemId])) )
            ) > 0,
            0,
            1
        )
    )
    

___DaysAged = 
if(SELECTEDVALUE('Test Data'[Transfer Qty])<0, BLANK(),  DATEDIFF(CALCULATE(MAX('Test Data'[Date]), ALLSELECTED('Test Data')),SELECTEDVALUE('Test Data'[Date]),DAY))

Link to file. 

Please mark as solution if so. Thumbs up for the effort are appreciated.

Kind regards, 
Steve. 

[alexricker0928]

Hi @stevedep - first off, thank you so much for the time and effort! Quite impressed with the complexity of the solution.

It does work when only looking at those first 4 rows of the data, however, when I add the rest of the dates back I run into issues.

Here's a screenshot of all records in my data set. The first -1 is substracted flawlessly, but the -3 should be taken from the records above respectively. Why would it not take that record into account?

[stevedep]

Hi @alexricker0928 ,

I see now. I have updated the file to work with running totals, the RT will accumulate purchases or sales. For each row the RT of a purchase is compared to the minimum RT value of the sales. If the sum of the two is below 0 this means that this item has been sold later in time.

See below. The link to the file is still the same. Kind regards, Steve. 

[amitchandak]

@alexricker0928 , need to check out the pbix.

In between can see if this blog can help

https://radacad.com/dax-inventory-or-stock-valuation-using-fifo

[alexricker0928]

@amitchandak  , thanks for taking a look!

I did read that article and attempted to implement the same code in my case (although I wasn't able to understand what the FIFO column was doing). I didn't get the expected results and wasn't sure what I needed to tweak. I can upload that version if need be. Let me know if you'd like to take a look at that one.