Forum Discussion
Stock Cover Months
Wcys02
Please find below the Stock Cover Measure. I have also attached the file.
Stock Coverage =
VAR __Opening = [Opening Stock]
VAR __T =
ADDCOLUMNS (
VALUES ( 'Sales Plan'[INDEX MONTH] ),
"Usage", [Stock Usage],
"Balance",
__Opening
- CALCULATE (
[Stock Usage],
WINDOW ( 0, ABS, 0, ALLSELECTED ( 'Sales Plan'[INDEX MONTH] ) )
)
)
VAR __T2 =
FILTER ( __T, [Balance] <= 0 )
VAR __Bal =
MINX ( __T2, [Balance] )
VAR __Month =
MINX ( __T2, [INDEX MONTH] )
VAR __P1 =
SWITCH (
TRUE (),
ISEMPTY ( __T2 ), 12,
__Bal = 0, __Month,
__Month - 1
+ MINX (
FILTER ( __T, 'Sales Plan'[INDEX MONTH] = __Month ),
DIVIDE ( [Usage] + [Balance], [Usage] )
)
)
RETURN
__P1
Many thanks for this! its spot on across model and branch (with right aggregation). How would I add get the stock coverage code to start calculating from a certain index month instead of the first month based on this code?
My plan is to update the report based on the actual month by creating the following measure to work out actual month number:
Actual month index number = IF((YEAR(TODAY())=2023),MONTH(TODAY()),MONTH(TODAY())+12)
Whereby Jan-Dec '23 = month 1 to 12
Jan-Dec '24 = month 13 to 24
I've tried then to incorporate this into the code but does not seem to be able to filter from month index 2 onwards, example by changing this part to:
VAR __T =
ADDCOLUMNS (
CALCULATETABLE(
VALUES('Targets (2)'[INDEX MONTH]),
'Targets (2)'[INDEX MONTH] >= 2
Thanks again,
Hi, apologies for not being clear. I basically want the reference index month to be dynamic based on the current month. For example, in December where the index/month number would be 12, it will start to calculate the stock cover using the sales plan based on index month 12 and onwards.
Excel link with example:https://we.tl/t-LJwkepZ63O
Hope this clarifies,
Many thanks