Forum Discussion

baravo's avatar
baravo
Helper I
6 years ago

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. 

 

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]))))
Need Help

3 Replies

    • baravo's avatar
      baravo
      Helper I
      6 years ago

      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.

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago
    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