Hello clever people,
I have been struggling to create an Ageing view of the stock on hand in my two retail stores. I really wish Sam had covered this in the Inventory Showcase… hint hint)
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.