Dynamic SLOB table using existing measures

watje255_ju — Mon, 13 Nov 2023 07:34:57 GMT

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

)

Attached is PBIX SLOB Example.pbix with the SLOB measures and desired results, SLOB Test.xlsx thanks alot

Re: Dynamic SLOB table using existing measures

lbendlin — Sat, 18 Nov 2023 18:17:36 GMT

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.

Have a look at the query that is produced for the virtual table. There are good videos on SQLBI.com on how to refactor queries to reduce cardinality quicker, and use fewer resources. You may even be able to get away with separate measures that are optimized for each cell in the result table.

topic Re: Dynamic SLOB table using existing measures in DAX Commands and Tips

Dynamic SLOB table using existing measures

Re: Dynamic SLOB table using existing measures