Sum future column values based on variable

[wi11iamr]

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.

[v-cherch-msft]

Hi @wi11iamr

It seems you may use VAR function like below post. If it is not your case, sample data will be helpful to provide an accurate solution. 

https://community.powerbi.com/t5/Desktop/Auto-select-previous-months/td-p/81963

Regards,

Cherie