Inventory aging calculation with FIFO

[baravo]

Hi! I've been searching through the internet and haven't been successful in finding a solution to my issue.

I need to calculate the aging of my current inventory. The inventory table contains inputs and outputs of the stock for each date, as below:

I managed to calculate the age and put it in age buckets (using helper table for aging groups), but the calculation is wrong, since it shows also the oldest input entries (these should be eliminated by the output entries that happened afterwards).

Helper table for aging Groups:

What I need to see in the end is what my inventory is now and how old it is, I don't what to see the whole history of movements for each item. I mean, those old items that have already been sold should not appear in the aging table.

I attach the example of 1 stock item, if anyone could help me with this, I would sincerely appreciate it. 

Thank you so much. 

ItemCode	InQty	OutQty	LocCode	Value	DocDate	Age	Movement	Aging
ZB1	22	0	MS	11542,3	viernes, 31 de mayo de 2019	430	22	180+
ZB1	6	0	MS-T4P	3147,9	viernes, 31 de mayo de 2019	430	6	180+
ZB1	3	0	MS	1978,68	lunes, 24 de junio de 2019	406	3	180+
ZB1	0	0	MS	0	lunes, 1 de julio de 2019	399	0	180+
ZB1	3	0	MS	1978,68	miércoles, 10 de julio de 2019	390	3	180+
ZB1	0	6	MS-T4P	-3147,9	martes, 23 de julio de 2019	377	-6	180+
ZB1	22	0	MS-T4P	11542,3	viernes, 26 de julio de 2019	374	22	180+
ZB1	0	22	MS	-11542,3	viernes, 26 de julio de 2019	374	-22	180+
ZB1	0	22	MS-T4P	-11542,3	lunes, 12 de agosto de 2019	357	-22	180+
ZB1	1	0	P-NAP	747,56	martes, 31 de diciembre de 2019	216	1	180+
ZB1	20	0	MS-T4P	1370,57	viernes, 24 de enero de 2020	192	20	180+
ZB1	44	0	MS-T4P	3015,25	viernes, 24 de enero de 2020	192	44	180+
ZB1	0	44	MS	-3015,25	viernes, 24 de enero de 2020	192	-44	180+
ZB1	0	20	MS	-1370,57	viernes, 24 de enero de 2020	192	-20	180+
ZB1	0	1	MS	-68,53	lunes, 10 de febrero de 2020	175	-1	=91-180
ZB1	0	1	MS	-68,53	miércoles, 19 de febrero de 2020	166	-1	=91-180
ZB1	0	1	MS	-68,53	jueves, 20 de febrero de 2020	165	-1	=91-180
ZB1	1	0	MS	747,56	miércoles, 25 de marzo de 2020	131	1	=91-180
ZB1	0	1	P-NAP	-747,56	miércoles, 25 de marzo de 2020	131	-1	=91-180
ZB1	0	1	MS	-181,7	martes, 14 de abril de 2020	111	-1	=91-180
ZB1	0	1	MS	-181,7	lunes, 27 de abril de 2020	98	-1	=91-180
ZB1	0	1	MS	-181,7	miércoles, 29 de abril de 2020	96	-1	=91-180
ZB1	1	0	P-NAP	181,7	martes, 5 de mayo de 2020	90	1	=61-90
ZB1	0	1	MS	-181,7	martes, 5 de mayo de 2020	90	-1	=61-90
ZB1	1	0	MS	181,7	miércoles, 6 de mayo de 2020	89	1	=61-90
ZB1	0	1	MS	-181,7	jueves, 7 de mayo de 2020	88	-1	=61-90
ZB1	0	1	MS	-181,7	lunes, 18 de mayo de 2020	77	-1	=61-90
ZB1	0	1	MS	-181,7	lunes, 1 de junio de 2020	63	-1	=61-90
ZB1	1	0	MS	181,7	martes, 2 de junio de 2020	62	1	=61-90
ZB1	0	1	P-NAP	-181,7	martes, 2 de junio de 2020	62	-1	=61-90

Formula for Aging column in Excel: =IF(H20<31;"=0-30";IF(H20<61;"=31-60";IF(H20<91;"=61-90";IF(H20<181;"=91-180";"180+"))))

DAX for Aging value (probably wrong): 

Aging Value = CALCULATE([Stock Value],

FILTER(DISTINCT('staging T4S_Inventory'[Age]),

COUNTROWS(FILTER(Groups, 'staging T4S_Inventory'[Age]>=Groups[Min]&&'staging T4S_Inventory'[Age]<=Groups[Max]))))

[AllisonKennedy]

What is the [Stock Value] formula?

If I understand correctly, you want to SUM the 'staging T4S_Inventory'[Movement] column for each Age Group? Is the formula you're using a COLUMN or MEASURE?

If you add a calculated COLUMN to the 'staging T4S_Inventory' table:
Age Group = MAXX(FILTER(Groups, 'staging T4S_Inventory'[Age]>=Groups[Min]&&'staging T4S_Inventory'[Age]<=Groups[Max]), Groups[Age])

Then put 'staging T4S_Inventory'[Age Group] in rows in a matrix and 'staging T4S_Inventory'[Movement] in values with SUM

[amitchandak]

@baravo , refer if this can help

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

[baravo]

@amitchandak This is definitely a great and helpful article. However, I still don't manage to perform the aging analysis correctly. How to achieve to see just what is currently on stock and how old it is (date from when it is).

I don't want to see the old entries that are already sold in the aging table.