I have a scenario where I am comparing the output of an item to its required output which is done weekly. I calculate the percentage of actual output against required output which can be seen in the Requirement Met % row below.
Where I'm struggling is with the recovery time metric. I'm trying to calculate the recovery time where Actual Output is short of the required output, and how long it takes to back fill a shortage.
So for week 1 in the image above, only 90% of the required output was fulfilled and therefore had a shortage of 10%. I'd then look at week 2 to fill week 1's shortage, however week 2 is also short 10% from its required output. So I'd then need to look at week 3 to fulfill week 1's shortage. This is possible as week 3 has more output than its required output (113%) and enough to fulfill week 1's shortage. This means it took an extra 2 weeks for week 1's requirement output to be met. The same would apply to week 2, it would look at the next week until its required output shortage can be met.
After week 3's required output and week 1's shortage are deducted from week 3's actual output, that leaves 3% which is not enough to fulfill week 2's required output. So I'd look at week 4 to see if it has enough leftover output to fulfill week 2's shortage...which it does. And so on and so on.
This would show the recovery time for each week.
I am working in alteryx but have not attempted to do this yet as I haven't worked out how to calculate this figure. If not alteryx I could do it in power BI but it's really how to calculate this metric that I'm after.
Thanks, Tom
