This is an example of the report I want.
Top row is days counting
I want to create an inventory aging report with a specific report date as the start of the aging, let's say today is July 31, 2023 but I want to have the report show as of July 1, 2023. This report should show how long the inventory has been sitting in BC as of a specific date.
Some facts that I understand(might be mistaken, please correct me):
1. Aging of an item is based on the day of receipt / positive adjustment, let's call this inventory date.
2. BC has a remaining qty function but that only works on Year to Date, or as of now.
a. This will not show you as of a specific date
b. This will not properly show you the aging of an item. If you move inventory through a journal, the item's remaining quantity will be attached to the journal entry, and that will be your inventory date instead of the actual purchase date.
3. There are ways to identify the inventory date of an item, and that's through Lot No., but the problem here is when items are not lot tracked.
a. When you move an item that is lot tracked, you'd still have that lot no. and you can look for its initial entry/ Purchase Receipt into BC, but if you don't have a lot no. it won't have any attributing value that it came from a particular purchase receipt.
For example, I have entries for Item 1. The batch column(column 2) is just to show that the item was received multiple times, and has entries associated with them, like a Lot No. in this case, assuming it's lot tracked.
Now my report should look like this for this item
It should show that I have 300 qty in the 0-90 day bucket
0 Qty in the 91-180 day bucket since I don't have a batch that was purchased between the date period
20 qty in the 181-365 day bucket because I purchased 200 in Dec and consumed 180.
and 50 for more than 365 days because I purchased 100 and consumed 50.
Any help would be appreciated