I have a Vendor table where each Vendor has a different StockForwardCoverRatio.
The StockForwardCoverRatio represents the number of months for which I must sum the ForecastSales to see how much cover my Vendor's current stock level provides.
E.g. We are in November and VendorA has a StockForwardCoverRatio of 2.5. This means that I must sum the ForecastSales for the next 2.5 months. So I wish to sum the full amounts for December and January, and then half the amount of February.
The below code uses a static assumption of only 1.5 month's cover where I sum the December value, and then calculate half the sum of the January value.
Target Stock =
CALCULATE (
[Remaining Forecast Cost],
NEXTMONTH( DATESMTD ( 'Calendar'[Date] ) )
)
+ DIVIDE(
CALCULATE (
[Remaining Forecast Cost],
NEXTMONTH( DATEADD(DATESMTD ( 'Calendar'[Date] ),[Target Stock Cover Whole Months],MONTH))),2,0
)
My trouble now is how to use the StockForwardCoverRatio value within such a measure that can, in the example of a 2.5 months StockForwardCoverRatio, sum the values from December and January, and then only half the value from February.
