Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rcorrales
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:

 

PartWk 20Wk 21Wk 22Wk 23Wk 24Wk 25Wk 26Wk 27Wk 28Wk 29Current inventory
1234604020201001050808070350
234590305006010020408020200
345630208060801080708080450
45670307020604010904050150

 

The result I want to get is the following:

 

PartWeeks of coverage 
12347
23454
34568
45674

 

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
v-xicai
Community Support
Community Support

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.

 

4.png

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:

 

5.png

 

 

 

 

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.

 

View solution in original post

3 REPLIES 3
v-xicai
Community Support
Community Support

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.

 

4.png

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:

 

5.png

 

 

 

 

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.

 

hello,

 

i have the samel issue and tryed using your formular. But this is not working fomr me.

what i am doing wrong?

 

sekka_1985_0-1681819143241.png

 

 

I have a table calling " Bedarf"

the is a column "Calendar Day") i like to rank this refering the calendar day by filtering " Material (Display)"

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!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.