My challenge is to evaluate the value of my aging stock.
I want to be able to see only the value of my stock that hasn’t been issued in the last 365 days.
If you see my PBIX I have created a measure that shows the date of the last issuance to production from stores and then another measure that counts the days between that date and today now. I also have a cumulative measure that shows me the value of that stock at that point in time. I now need to be able to know what of that cumulative value is aged stock (>= 365 days old)
What I want is to be able to evaluate/see is this by month of the year for all products, as shown in the attached screenshot from excel.
Hi @Krays23. I took a look at your issue for a bit, but unfortunately wasn’t able to make any progress. Being unfamiliar with the dataset prevented me (even with your well laid-out data model) from properly visualizing a “Transactions” table and creating relevant aging measures. Perhaps a simpler dataset will help other forum members progress on your issue.
A couple of quick notes:
(1) I didn’t see a “Products” table; I’m guessing you mean a “Material”?
(2) I saw some local data tables, so turned off the option for Current File \ Data Load \ Time intelligence \ Auto date/time.
(3) Your dataset goes back to 2010; your (eventual) report will perform better with a smaller amount of data; I’d filter the dataset to only the last couple of years, or whatever period is necessary to answer the questions that are the reports’ intent
Could you confirm the definition “stock that hasn’t been issued in the last 365 days” is according the FIFO-principle (First In First Out) ?
Could you confirm that the calculation has to be :
Total stock end of period -/- Purchases during the last 365 days (can not be less then 0)
Please have a look at attached Excel example with stock at least 3 days old and random purchases and sales, to clarify the concept Excel concept FIFO calc.xlsx (18.3 KB)
If 2 is the concept, which of your 58 Stock Movement Types have to be considered as PURCHASES ?
