Forum Discussion
Age FIFO Inventory
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.
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?
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.
