Cumulative Sum that Resets based on its own value?

[Hazenm]

I've got a real tricky problem that I'm trying to solve for that I'm hoping someone can help me out with. 

I am trying to find a way to create  cumulative sum that resets when its own value is less than or equal to 0
There may be a much simpler way to handle this problem, and if there is a much different and better path, I'm all ears! So I'll more fully illustrate my problem in case there is a much better way to handle this. 

First off, I cannot use Calculated Columns at all for this solution because I need to be able to modify my data with user entered parameters - and I also have a Direct Query table that will change the data. 

I'm building a warehouse capacity model for my company. In the model, users can input different variables such as Takt Time, Headcount, Total Hours, etc. I've built a forecast quantity in there that is working fine as well. The model shows us, based on variables, how many units we can ship each week, and if we can't ship, what is required. 

My Problem/The difficulty lies in cumulative effect of when the model predicts that we fall behind on shipping units. If we can't ship everything in a given week, we have to ship it the next week. So if we miss a week, then the "Forecasted Units" for the next week NEED to include the missed shipments from the week prior. Once again, if we miss the next week, we have to add this new total to the next week. 

I can illustrate this in excel like this: 
(Ignore the fact that the capacity is very unrealistic. I built a complicated random example that is a stronger test than my actual data)

The formula in excel for the output here is: 
C = Forecast Qty
D = Capacity
K = Output
Current row for this formula = 36
=MAX(C36-D36+K35,0)

So I have to refer back to the same column in excel to get this formula to work. 
Basically, you could think of column K / Output column AS a cumulative sum that resets when its own value is less than or equal to 0

Any ideas? I'm totally stumped on this one! I keep thinking I've found an answer and then I realize it's circular, and I end up back at this. 

Ship Week		Fcst Qty Units		Capacity		Output Should be
3/15/2021 0:00		12959		11444.36364		1514.636364
3/22/2021 0:00		15574		11444.36364		5644.272727
3/29/2021 0:00		15086		11444.36364		9285.909091
4/5/2021 0:00		16926		15000		11211.90909
4/12/2021 0:00		14982		15000		11193.90909
4/19/2021 0:00		13215		15000		9408.909091
4/26/2021 0:00		13304		15000		7712.909091
5/3/2021 0:00		11083		17000		1795.909091
5/10/2021 0:00		6958		20000		0
5/17/2021 0:00		6121		9536.969697		0
5/24/2021 0:00		17000		4000		13000
5/31/2021 0:00		4581		4768.484848		12812.51515
6/7/2021 0:00		4024		18000		0

[Anonymous]

Use composite models in Power BI Desktop - Power BI | Microsoft Docs

[AlexisOlson]

Unless I'm missing something, I'm fairly sure using a composite model won't help here. You can certainly write recursive logic in the M language, but such a transformation would not likely be compatible with DirectQuery nor can the M code read slicers or filters from a report page.

[Hazenm]

@Anonymous - I am intrigued. When you say composite model though, you're speaking about the direct query components. Would this somehow allow for user parameters to affect this recursive calculation? 
I suppose I could get rid of all the user parameters in the report and force them to modify the data in the actual direct query database. But less than ideal. 

[Hazenm]

@AlexisOlson great work on that other problem! And I looked at the Fibonacci sample example as well. Great work there, too. I have been banging my head trying to figure out how to solve for this problem non-recursively, but nothing yet. Every time I think I have found a pattern, it ends up recursive again. I keep thinking maybe I can find a 'reset date' value based on when the cumulative fcst qty is greater than the cumulative capacity. We could find the first value from the start, but then we would need to again reset both of those values at that point and work to find the next value, so once again being recursive. 

I have come up with a really messy temporary solution. Although, after doing more research on trying to work around recursive problems in dax, maybe this is a permanent solution. 
Although, I'm curious if anyone has any "less messy" messy solutions to this problem, also..

So here's what I've done: 

First I created a virtual table within the measure that stores: 

Ship Week | Fcst (with my slider increase) | Capacity | Remain to Ship |
Remaining to ship = Fcst - Capacity (allowing negatives) 

From here I just said that realistically I don't need to see indefinitely into the future, but if I can calculate out even 12-15 weeks, that will be good enough for company needs. 
So then the mess begins with a variable to store each week's capacity from today's week: 

VAR v1 =

SUMX(

            FILTER(

                        virtualCapTable,

                        'Capacity Table'[Ship Week] = currentWeekStart

            ),

            [Remain to Ship]

)
VAR v2 = 

MAX(

            SUMX(

                        FILTER(

                                    virtualCapTable,

                                    'Capacity Table'[Ship Week] = currentWeekStart + (7 * 1)

                        ),

                        [Remain to Ship]

            ) + v1,

            0

)

Etc, through to VAR v12, with each v adding the prior v

Then finally

VAR cumulRemain =

SWITCH(

           rowWeekStart,

           currentWeekStart,

           V1,

           currentWeekStart+(7*1),

           V2,

           currentWeekStart+(7*2),

           V3,

           currentWeekStart+(7*3),

           V4,

           currentWeekStart+(7*4),

           V5,

           currentWeekStart+(7*5),

           V6,

           currentWeekStart+(7*6),

           V7,

           currentWeekStart+(7*7),

           V8,

           currentWeekStart+(7*8),

           V9,

           currentWeekStart+(7*9),

           V10,

           currentWeekStart+(7*10),

           V11,

           currentWeekStart+(7*11),

           V12,

           -1

)

I've given a negative value to signify that the cumulative capacity is not being calculated, either in the future or in the past. 

It works! (yikes.)

Sidenote on this, but related, when I tried to create the virtual table, I realized that I couldn't create variables outside of the SUMMARIZE function to get the table to build correctly. Is there a way to declare variables inside a table like this? I also know that you should use ADDCOLUMNS instead of SUMMARIZE when possible, but my data is in days on the other tables, so I was aggregating. 
Example here, the formula for capacity: 

"New Capacity",

((SUMX(

     FILTER(

     cr872_sitecapacity2,

     cr872_sitecapacity2[Fiscal Week] = MAX('Capacity Table'[Fiscal Fcst Week])

     ),

     cr872_sitecapacity2[Headcount] * (cr872_sitecapacity2[Working Hours]*60*60)

    ) * ratio) /

    (farm +PickShip)) +

    ((SUMX(

     FILTER(

     cr872_sitecapacity2,

     cr872_sitecapacity2[Fiscal Week] = MAX('Capacity Table'[Fiscal Fcst Week])

     ),

     cr872_sitecapacity2[Headcount] * (cr872_sitecapacity2[Working Hours]*60*60)

    ) * (1 - ratio)) / (assemble + PickShip)),

If I try to declare this as a variable outside of the table it stores the wrong data, which makes sense, but wondering if I can somehow declare this as "Capacity' inside the table. OR can I reference this new column somehow when creating a subsequent column in the virtual table itself? When I create "Remain to Ship" column, I had to just copy the formula for "Fcst Units" and then do a subtraction of a copy of that entire formula for capacity. Not the end of the world, but again, messy, and wondering if a cleaner solution exists. 

Thanks all for looking in on this with me!

[AlexisOlson]

If you're willing to create as many variables as there are weeks in your table, then it is possible since you are essentially doing the recursion by hand when defining all of the variables.

[Anonymous]

@Hazenm 

Instead of trying to crack this nut in DAX... you should have created a solution in Power Query, which has a language - M - suitable for this kind of recursive calculations and the solutions are much faster than in DAX. It would be soooo much easier to do in M.

There's one more reason why it should not be done in DAX: MAINTENANCE. I just wonder... how much of this code you'll understand when you come back to it in 6 months... not to mention others!

[Hazenm]

Ah, but I think you missed the second paragraph in my initial post @Anonymous :
"First off, I cannot use Calculated Columns at all for this solution because I need to be able to modify my data with user entered parameters - and I also have a Direct Query table that will change the data. "
For the same reason, I cannot use power query. I need the capacity variable & the forecast variable to change on the fly based on a very large number of user inputs. Some which I have referenced via the dataverse and direct query, other which are user parameters. 
Unless I am mistaken, if I did these calculations in M, I would have to refresh the entire report every time a user wanted to try a different variable, correct?

[Anonymous]

If you use a composite model, the above limitations are gone... I'm not aware of how much dynamics you need for the measure, hence my suggestion. I don't right now have time to read this thoroughly. So, my suggestions might be a bit off.

[Anonymous]

Since recursion is not allowed in DAX (apart from a very special one called "sideways recursion") and there's no closed formula for this (because your measure depends on MAX at each step), you don't have a choice other than to calculate this in Power Query. Sorry.

[AlexisOlson]

I've run into similar problems and I'm fairly sure that the answer is that this is not possible in DAX since the definition is recursively defined and doesn't have a clean non-recursive refactoring, at least as far as I can see. The resetting at zero breaks the techniques I sometimes use to refactor.