Showing results for 
Search instead for 
Did you mean: 
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:



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. 


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]))))
Super User III
Super User III

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



Check out my Data Stories Gallery Christmas Report: Are you on Santa's Naughty or Nice List?


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.

Super User IV
Super User IV

@baravo , refer if this can help

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!

@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


Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors