Stock Ageing Analysis Using FIFO Calculation

[Anonymous]

Hello clever people,

I have been struggling to create an Ageing view of the stock on hand in my two retail stores. 

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: https://drive.google.com/drive/folders/1LaCQk2WwaUl9Wskd-8PyfO0CNcVbvZEi?usp=sharing

Please let me know if you need any additional information to be able to assist.

Cheers,

Phil

[Anonymous]

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.

Thanks.

PS. I can't acccess files on shared drives like Google or OneDrive from work.

[Anonymous]

Hi Daxer,

Thank you for your interest in assisting, I really appreciate it!  I do not have permission on the forum to upload file (not sure why Microsoft does not allow everyone to do this)

The model is fairly simple:

Lookup Tables:

Dates: simple date table with dates, months, years etc

Range: product range table with SKU and Product Name

Stores: store ID and Store Name

Fact Tables: 

Dispatches from DC:

SOH (note that this is the closing balance each day)

Sales:

Dispatches from DC

Finally, I have a table with the age buckets I would like to classify the SOH into:

The end result would look something like this:

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

Cheers, 

Phil

[Anonymous]

Is this the logic for your problem?

https://docs.microsoft.com/en-us/dynamics365/supply-chain/cost-management/inventory-aging-report

[amitchandak]

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

Hi Amitchandak,

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. 

[amitchandak]

@Anonymous , i am try to check option of bucketing like

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

something like this

Age bucket qty sum = CALCULATE(Sumx(filter(VALUES(SOH[Invtid]), [Age] >=min('Agebucket'[Age bucket start]) && [Age] <=max('Agebucket'[Age bucket end])),SOH[Value Unit]))

But i doubt the lowest level