Stock Ageing Analysis Using FIFO Calculation

Question

Hello clever people,I have been struggling to create an Ageing view of the stock on hand in my two retail stores.&nbsp;&nbsp;I have the daily Stock on Hand (SOH) and Sales for each store by date and SKU, I also have the Dispatches from the DC that supplies the stores, I do not have a receipt of the stock when it arrives into the store so I will assume that the good reach the stock on the same day it is dispatched.There was some stock on hand in the store on the date that my reporting starts (01/01/2020) and I will consider all this stock to be 1 day old on the 01/01/2020.The result I am looking for is to populate a set of Age Buckets with the number of units and value of stock in each bucket. The buckets are 0-15 days old, 16-30 days old, 31 to 45 days old etc.I have found many examples in SQL about how to achieve this but unfortunately, I have no SQL experience and I am sure that this can be created in DAX if someone could steer me in the right direction.I have made a test dataset and placed it in the following location:&nbsp;https://drive.google.com/drive/folders/1LaCQk2WwaUl9Wskd-8PyfO0CNcVbvZEi?usp=sharingPlease let me know if you need any additional information to be able to assist.Cheers,Phil

amitchandak · Answer

Anonymous , refer if these can help
https://radacad.com/dax-inventory-or-stock-valuation-using-fifo
https://community.powerbi.com/t5/Desktop/FIFO-Stock-Calculation/td-p/458160
https://forum.enterprisedna.co/t/share-valuation-using-fifo-method/3853
https://www.youtube.com/watch?v=xw5GwVQHpPs

anonymous · Answer

Hi Amitchandak,&nbsp;Thanks for the links... Unfortunately, I have been through all of them already over the past week and they do not deal with the same problem.&nbsp;

amitchandak · Answer

Anonymous , i am try to check option of bucketing like 
&nbsp;
https://www.daxpatterns.com/dynamic-segmentation/https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
&nbsp;
something like this
Age bucket qty sum = CALCULATE(Sumx(filter(VALUES(SOH[Invtid]), [Age] &gt;=min('Agebucket'[Age bucket start]) &amp;&amp; [Age] &lt;=max('Agebucket'[Age bucket end])),SOH[Value Unit]))
&nbsp;
But i doubt the lowest level

anonymous · Answer

Hi there.You say "I have found many examples in SQL about how to achieve this […]". Would you please give me a link to this SQL? Also, it would be advisable that you post your model here. It can be simplified to only those tables and relationships that matter for this task. Also, you could strip the model of any columns that are just nuisance. That would definitely help.&nbsp;Thanks.&nbsp;PS. I can't acccess files on shared drives like Google or OneDrive from work.

anonymous · Answer

Hi Daxer,&nbsp;Thank you for your interest in assisting, I really appreciate it!&nbsp; I do not have permission on the forum to upload file (not sure why Microsoft does not allow everyone to do this)&nbsp;The model is fairly simple:&nbsp;&nbsp;Lookup Tables:Dates: simple date table with dates, months, years etcRange: product range table with SKU and Product NameStores: store ID and Store Name&nbsp;Fact Tables:&nbsp;Dispatches from DC:&nbsp;&nbsp;SOH (note that this is the closing balance each day)&nbsp;Sales:&nbsp;Dispatches from DC&nbsp;Finally, I have a table with the age buckets I would like to classify the SOH into:&nbsp;The end result would look something like this:&nbsp;I think the SQL query that matches my requirement the closest is http://www.kodyaz.com/sap-abap/stock-aging-using-sqlscript-on-sap-hana-database.aspx&nbsp;Cheers,&nbsp;&nbsp;Phil&nbsp;&nbsp;

Forum Discussion

Stock Ageing Analysis Using FIFO Calculation

6 Replies

Related content

Age Bins

Cohort analysis

Calculating quote ages

Calculate Age column

stock ageing