Convert Excel Formula to DAX

Question

I am trying to write a DAX formula for weeks of coverage of inventory and run the formula out to future weeks. If I have 7 weeks of inventory on hand, how many weeks of inventory will I have on hand 3 weeks from now?

I have the formula in Excel but most of the functions arent usable in PowerBI.

My Calc line works correctly =IFERROR(MATCH(1,IF(MMULT(IF(COLUMN(C10:$X10)<=TRANSPOSE(COLUMN(C10:$X10)),C10:$X10,0),TRANSPOSE(COLUMN(C10:$X10))^0)<=B8,0,1),0)-1,COLUMNS(B12:$X12))

Based on the Net Available starting point for each week. How many weeks of the running total of sales orders + forecast will I have before the sum is greater than the starting point balance? In the case of week 4, the running total in Week 12 (4885) is the first point it will exceed the Week 4 ending balance (4371)

Any help on an a DAX equivalent or easier formula than using a matrix with multiple arrays is appreciated!

lbendlin · Answer

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

finding_nenno · Answer

WeekWeek 4 2024Week 5 2024Week 6 2024Week 7 2024Week 8 2024Week 9 2024Week 10 2024Week 11 2024Week 12 2024Week 13 2024&nbsp;On Hand Qty&nbsp;&nbsp;&nbsp; 3,742&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Production Qty&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 704&nbsp;&nbsp; 1,600&nbsp;&nbsp;&nbsp; 1,600&nbsp;&nbsp; 1,600&nbsp;&nbsp; 1,600&nbsp;&nbsp; 1,600&nbsp;&nbsp; 1,600&nbsp;&nbsp;&nbsp;Sales Orders Qty&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (34)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (20)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (12)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (10)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (10)&nbsp;&nbsp;&nbsp;Forecast Qty&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (41)&nbsp;&nbsp;&nbsp;&nbsp; (533)&nbsp;&nbsp;&nbsp;&nbsp; (521)&nbsp;&nbsp;&nbsp;&nbsp; (533)&nbsp;&nbsp;&nbsp;&nbsp; (523)&nbsp;&nbsp;&nbsp;&nbsp; (681)&nbsp;&nbsp;&nbsp;&nbsp; (681)&nbsp;&nbsp;&nbsp;&nbsp; (680)&nbsp;&nbsp;&nbsp;&nbsp; (681)&nbsp;&nbsp;&nbsp;&nbsp; (680)&nbsp;Current Weeks of Supply&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;Net Available&nbsp;&nbsp;&nbsp; 4,371&nbsp;&nbsp; 5,418&nbsp;&nbsp; 4,885&nbsp;&nbsp; 5,952&nbsp;&nbsp; 7,019&nbsp;&nbsp; 7,938&nbsp;&nbsp; 8,857&nbsp;&nbsp; 9,767&nbsp;&nbsp; 9,086&nbsp;&nbsp; 8,406&nbsp;Orders &amp; Forecast (Sum)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 75&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 553&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 533&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 533&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 533&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 681&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 681&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 690&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 681&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 680Desired Result787891011121110

Forum Discussion

Convert Excel Formula to DAX

2 Replies

Related content

Converting Excel Formulas to DAX

Convert Excel formula to DAX

Convert excel formula to Power Query

Convert Excel formula to Power Query

Convert Excel formula to DAX

Week	Week 4 2024	Week 5 2024	Week 6 2024	Week 7 2024	Week 8 2024	Week 9 2024	Week 10 2024	Week 11 2024	Week 12 2024	Week 13 2024
On Hand Qty	3,742
Production Qty	704	1,600		1,600	1,600	1,600	1,600	1,600
Sales Orders Qty	(34)	(20)	(12)		(10)			(10)
Forecast Qty	(41)	(533)	(521)	(533)	(523)	(681)	(681)	(680)	(681)	(680)
Current Weeks of Supply	7	6	5	4	3	2	1
Net Available	4,371	5,418	4,885	5,952	7,019	7,938	8,857	9,767	9,086	8,406
Orders & Forecast (Sum)	75	553	533	533	533	681	681	690	681	680
Desired Result	7	8	7	8	9	10	11	12	11	10