Hi.
I’m trying to get the Weeks Cover of stock, based on current stock on hand, Open Purchase Orders, and Demand Forecast. I do not have a PBIX to share, as most of the data is a Direct Query.
I did manager to get it working, on a filtered dataset. If I remove the filters, it takes a long time to query and eventually times out due to memory. So my queries must improve.
In the screenshot here is partly what I need. The user will, however, select a specific week (here the 25/12/2023) and then only see the three or four columns for each product (here national) (which must also work when dragging in regions and warehouses).
We are currently in Week 0 and the current stock with incoming purchase orders and demand forecast will last for another 19 weeks and it’ll reduce by one every other week.
Here’s the measure for Weeks Cover (EDNA did most of it), but maybe there’s an easier or faster way.
Weeks Cover =
VAR Week =
IF ( [Closing Stock] >= 0, MAX ( Date445[Week Index] ), BLANK () )
VAR Week2 =
CALCULATE (
CALCULATE (
MAX ( ‘Date445’[Week Index] ),
TOPN (
1,
FILTER ( ALL ( ‘Date445’[Week Index] ), [Closing Stock] > 0 ),
[Closing Stock], ASC
)
),
ALL ( Date445 )
)
RETURN
IF(ISBLANK(Week),BLANK(),
IF ( Week2 >= 52, 52, week2 - Week )
)
In theory, it should only count the remaining weeks until closing stock <0. Or the max of week index while the closing stock is positive minus the week index. the closing stock is a measure, the week index a column.
Appreciate any suggestions.
Cheers Kat.