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|
The result I want to get is the following:
|Part||Weeks of coverage |
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.
I found a way to do it in Excel, the formula is the following:
How can I translate this into PowerBI?