Weeks of inventory forecast coverage

[rcorrales]

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?

[v-xicai · Accepted solution]

Hi @rcorrales 

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:

You can download my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EY3frJErIPtMjksEOS... .

Best Regards,

Amy

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

[rcorrales]

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!