Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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.
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 , 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
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.
@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
User | Count |
---|---|
56 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |