cancel
Showing results for
Did you mean: Frequent Visitor

## Weeks of inventory forecast coverage

Hi everyone,

First-time poster here, I've been using PowerBI for a few months, but I have no idea how to tackle this problem. I need to calculate how many weeks will my current inventory cover for a customer forecast demand.

My data source looks like this:

 Part Wk 20 Wk 21 Wk 22 Wk 23 Wk 24 Wk 25 Wk 26 Wk 27 Wk 28 Wk 29 Current inventory 1234 60 40 20 20 100 10 50 80 80 70 350 2345 90 30 50 0 60 100 20 40 80 20 200 3456 30 20 80 60 80 10 80 70 80 80 450 4567 0 30 70 20 60 40 10 90 40 50 150

The result I want to get is the following:

 Part Weeks of coverage 1234 7 2345 4 3456 8 4567 4

The logic I'm coming up with is to sum the first week on the source, and if it is less than the current inventory, add another week, until the week cumulative sum is higher than the current inventory, then return the number of weeks added.

I'm not sure if this is possible, it makes sense in my head but I have no idea how to attack this problem. If you could provide some hints on how to solve I would really appreciate it.

Thanks!

Edit:

I found a way to do it in Excel, the formula is the following:

`=MATCH(L2,SUBTOTAL(9,OFFSET(B2,,,,COLUMN(B2:L2)-COLUMN(B2))),1)-1`

How can I translate this into PowerBI?

1 ACCEPTED SOLUTION  Community Support

Following steps below to achieve your requirement.

1.Upivot the week column, click on column Wk20-Wk29, click "Unpivot Columns", rename this result column with "Week". Don't forget to click the "Close & Apply" button. 2.Create columns and measure like DAX below.

Column: Rank = CALCULATE(COUNT(Table1[Value]),FILTER(ALLSELECTED(Table1),Table1[Part]=EARLIER(Table1[Part])&&Table1[Week]<=EARLIER(Table1[Week])))

Column: Cumulative_sum = CALCULATE(SUM(Table1[Value]),FILTER(ALLSELECTED(Table1),Table1[Part]=EARLIER(Table1[Part])&&Table1[Week]<=EARLIER(Table1[Week])))

Measure: Weeks of coverage = CALCULATE(FIRSTNONBLANK(Table1[Rank],1)-1,FILTER(ALLSELECTED(Table1),Table1[Part]=MAX(Table1[Part])&&[Cumulative_sum]>Table1[Current inventory]))

3.Result: Best Regards,

Amy

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

2 REPLIES 2  Community Support

Following steps below to achieve your requirement.

1.Upivot the week column, click on column Wk20-Wk29, click "Unpivot Columns", rename this result column with "Week". Don't forget to click the "Close & Apply" button. 2.Create columns and measure like DAX below.

Column: Rank = CALCULATE(COUNT(Table1[Value]),FILTER(ALLSELECTED(Table1),Table1[Part]=EARLIER(Table1[Part])&&Table1[Week]<=EARLIER(Table1[Week])))

Column: Cumulative_sum = CALCULATE(SUM(Table1[Value]),FILTER(ALLSELECTED(Table1),Table1[Part]=EARLIER(Table1[Part])&&Table1[Week]<=EARLIER(Table1[Week])))

Measure: Weeks of coverage = CALCULATE(FIRSTNONBLANK(Table1[Rank],1)-1,FILTER(ALLSELECTED(Table1),Table1[Part]=MAX(Table1[Part])&&[Cumulative_sum]>Table1[Current inventory]))

3.Result: Best Regards,

Amy

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

Thank you so mucho @v-xicai. Sorry it took me this long to reply, I was out of the office and I'm just catching up.

I'll study this solution so I can figure out the logic and the coding behind it, but this accomplishes the goal I was looking for. I really appreciate your quick response!  