Forum Discussion

alexricker0928's avatar
alexricker0928
Frequent Visitor
5 years ago

Age FIFO Inventory

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 OrderDateItemIdTransfer Qty
1000-0000723/20/20200402521
1000-0000735/30/20200402521
1000-0000746/15/20200402521
1000-0018816/16/2020040252-1
1000-0018869/11/2020040252-3
1000-0000519/11/20200438094
1000-0000619/11/20200402522
1000-0000629/11/20200402521
1000-0000719/12/20200402522

 

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 OrderDateItemIdTransfer QtyFIFODays Aged
1000-0000723/20/20200402521089
1000-0000735/30/20200402521118
1000-0000746/15/2020040252112
1000-0018816/16/2020040252-1THIS 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!!

 

5 Replies

  • stevedep's avatar
    stevedep
    Memorable Member
    5 years ago

    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's avatar
      alexricker0928
      Frequent Visitor
      5 years ago

      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's avatar
        stevedep
        Memorable Member
        5 years ago

        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. 

    • alexricker0928's avatar
      alexricker0928
      Frequent Visitor
      5 years ago

      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.