Hello,
I am trying to calculate slow moving obsolete stock, which I have done by calculating the shelf life remaining for each batch based off the slicer date. I would like this to be dynamic so that when the reporting date changes the SLOB calculations update.
The shelf life difference between the manufacturing date and the expiry date, and the remaining shelf life is the difference between the expiry date and the date selected.
We will provide for items with less than 65% of shelf life remaining at reporting date, and would like to see the stock aged by shelf life intervals, >12 month shelf life remaining, 13-18 month Shelf life remaining etc
The calculation works ok when I have all the batch numbers selected, and can filter by 65% on SLR%, but I would like the data to show the SLOB at product level, and when I delete the batch, all items are filtered out.
I also want to be able to show the aging of SLOB in a table form by the month bands, which isn't possible by just filtering the SLR% measure in the filter pane.
I have created a table using the below, but it isn't dynamic, so when i change the reporting date (date slicer) the SLR doesn't update? Also, I am wanting to graph SLOB by week, but I am not sure of the measure to calculate the culmulative sum off all previous weeks?
I am not sure if this is the best approach as I have a large dataset, so when I try to use a DAX virtual table the "visual exceeds resrouces error"shows.
SLOB =
ADDCOLUMNS (
FILTER (
SUMMARIZE (
FactInventoryValueView,
DimDate[DateKey],
FactInventoryValueView[Batch Number],
DimProduct[Group],
DimProduct[Product Key]
),
DimProduct[Group] = "Finished Goods"
),
"Expiry Date", [Expiry Date],
"Manuf. Date" , [Manuf. Date],
"Shelf Life Remaining", [Shelf Life Remaining],
"SLR %",[SLR %],
"Months Since Production", [Months since production],
"Months Since Production2", [MonthsSinceProduction DK],
"Quantity", [Stock Movement],
"$ Value", [$ Movement]
)
Example of the SLOB aging bands:
SLOB Between 19 and 24 Months = // shelf life remaining between 19 and 24 months
CALCULATE(
[Stock on hand SLOB],
FILTER(
SLOB,
[Shelf Life Remaining] >= 19 && [Shelf Life Remaining] <= 24
)
)
