Weeks coverage based on stock and forecast

[mr_oli]

Hello!

I need to calculate stock coverage in weeks based on forecast.

I have two tables

Stock table:

https://i.imgur.com/NDcIK86.png 

and forecast table (column week sort is sorted):

https://i.imgur.com/6SOupX0.png 

and based on these two tables I need to know for how many weeks I am covered with my stock.

Logic should be like this:

https://i.imgur.com/hRqJQKg.png 

[v-zhenbw-msft]

Hi @mr_oli ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If the above posts help, please kindly mark it as a solution to help others find it more quickly.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhenbw-msft]

Hi @mr_oli ,

If you want to display the total in each row, we can create a measure to meet your requirement.

Measure = 
IF(
    ISFILTERED('Table'[Forecast]),
    CALCULATE(SUM(forecast[Forecast]),FILTER(forecast,forecast[week column]=MAX('Table'[Forecast])))+0,
    CALCULATE(SUM(forecast[Forecast])))

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[v-zhenbw-msft]

Hi @mr_oli ,

We can create a table and two measures to meet your requirement.

1. Create a table that contains Forecast week and Forecast week + days.

2. And create a relationship between forecast table and stock table.

3. Then we can create two measures to get the result.

Total = 
var _max_w_sort = MAX(forecast[Week sort])
var _max_w = CALCULATE(MAX(forecast[week column]),FILTER(forecast,forecast[Week sort]=_max_w_sort))
var _weekadd = CALCULATE(MAX('Table'[Custom]),FILTER('Table','Table'[Forecast]=_max_w))
return
RIGHT(_weekadd,LEN(MAX('Table'[Custom]))-2) &" "& "weeks"

Measure = 
IF(
    ISFILTERED('Table'[Forecast]),
    CALCULATE(SUM(forecast[Forecast]),FILTER(forecast,forecast[week column]=MAX('Table'[Forecast])))+0,
    [Total])

The Product 1 on week 04, so the coverage is +7. Don’t know our understanding is correct.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[mr_oli]

Thank you very much for you answer but I have one question.

Why for product 1 coverage is showed as 7 weeks? Sum of forecast is 9 so we still will have 1 on stock

[v-zhenbw-msft]

Hi @mr_oli ,

Sorry for that we have a little confused.

In your screenshots, why the product id 1 coverages more than 13 weeks. It stopped in forecast wk + 7. We think the coverage will be 7.

Or you want to get the total forecast? In Product id 1, it will show 2+2+5 = 9, in Product id 2, it will show 3+5+2+2 = 12.

Could you please explain your desire result screenshot?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mr_oli]

---

@v-zhenbw-msft wrote:

In Product id 1, it will show 2+2+5 = 9, in Product id 2, it will show 3+5+2+2 = 12.

That is correct, thats why we are still covered for 13 or more weeks - because on week week 10 Stock > Sum of forecast 🙂

If you will put in Product 1, stock which is equal 20 and for every forecast week put 1, you still will be covered for more than 13w, because sum of forecast is < stock

Forecast is like consumption so we still will have stock