cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Inventory aging calculation with FIFO

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:

baravo_0-1596530786154.png

 

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).

 

baravo_4-1596531240278.png

Helper table for aging Groups:

baravo_0-1596532597955.png

 

 

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. 

 

ItemCodeInQtyOutQtyLocCodeValueDocDateAgeMovementAging
ZB1220MS11542,3viernes, 31 de mayo de 201943022180+
ZB160MS-T4P3147,9viernes, 31 de mayo de 20194306180+
ZB130MS1978,68lunes, 24 de junio de 20194063180+
ZB100MS0lunes, 1 de julio de 20193990180+
ZB130MS1978,68miércoles, 10 de julio de 20193903180+
ZB106MS-T4P-3147,9martes, 23 de julio de 2019377-6180+
ZB1220MS-T4P11542,3viernes, 26 de julio de 201937422180+
ZB1022MS-11542,3viernes, 26 de julio de 2019374-22180+
ZB1022MS-T4P-11542,3lunes, 12 de agosto de 2019357-22180+
ZB110P-NAP747,56martes, 31 de diciembre de 20192161180+
ZB1200MS-T4P1370,57viernes, 24 de enero de 202019220180+
ZB1440MS-T4P3015,25viernes, 24 de enero de 202019244180+
ZB1044MS-3015,25viernes, 24 de enero de 2020192-44180+
ZB1020MS-1370,57viernes, 24 de enero de 2020192-20180+
ZB101MS-68,53lunes, 10 de febrero de 2020175-1=91-180
ZB101MS-68,53miércoles, 19 de febrero de 2020166-1=91-180
ZB101MS-68,53jueves, 20 de febrero de 2020165-1=91-180
ZB110MS747,56miércoles, 25 de marzo de 20201311=91-180
ZB101P-NAP-747,56miércoles, 25 de marzo de 2020131-1=91-180
ZB101MS-181,7martes, 14 de abril de 2020111-1=91-180
ZB101MS-181,7lunes, 27 de abril de 202098-1=91-180
ZB101MS-181,7miércoles, 29 de abril de 202096-1=91-180
ZB110P-NAP181,7martes, 5 de mayo de 2020901=61-90
ZB101MS-181,7martes, 5 de mayo de 202090-1=61-90
ZB110MS181,7miércoles, 6 de mayo de 2020891=61-90
ZB101MS-181,7jueves, 7 de mayo de 202088-1=61-90
ZB101MS-181,7lunes, 18 de mayo de 202077-1=61-90
ZB101MS-181,7lunes, 1 de junio de 202063-1=61-90
ZB110MS181,7martes, 2 de junio de 2020621=61-90
ZB101P-NAP-181,7martes, 2 de junio de 202062-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]))))
3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Inventory aging calculation with FIFO

@baravo , refer if this can help

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User II
Super User II

Re: Inventory aging calculation with FIFO

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

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted
Helper I
Helper I

Re: Inventory aging calculation with FIFO

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors