It’s best if your source system does not allow annual loans to exceed the annual limit, or provide you with a limited annual amount of leave directly (instead of trying to calculate it in Power BI).
Even if the source system does not save a limited annual vacation amount, it may be easier to calculate in a query using SQL than using Power BI. (I would recommend a separate question for this.)
Why am I saying this?
In Excel, the total (or aggregate) amount is calculated line by line based on the value from the previous line. This makes it easy to "override" the current amount, and this override applies to each subsequent line. For instance. you can limit the current total amount to 20, and the cell under it acts as if the total amount is 20. The cells under the lid have no idea that the total amount is not equal to 20.
In DAX, the total amount is calculated independently of each row (which means that each row scans all rows up to the current row date and calculates what the actual total amount is). This makes it impossible to redefine the current total amount (for example, closing it to 20) and adjust the adjusted total flow to the next line. The next line always knows what the real total is.
It is impossible to tell DAX that the previously calculated total number of starts and add to it, because the column cannot refer to it (as mentioned by user5226582, this is a circular dependency). DAX, unlike Excel, runs a column of calculations by column, not a cell by cell, so it cannot use the output of a column as input for the same column.
Dirty and incomplete workaround
A dirty workaround will depend on how many times Annual loans can be ignored. Each time, all or part of the annual loan is ignored, it adjusts the current amount for all subsequent cells.
For example, from May 1 to 17, the true total is 20.5, but you throw 0.5. This means that all future lines are based on a total of 20 as of May 1 -17, not 20.5.
You could create a settlement column that identifies the first time the total amount is limited (May 1, 2017). Then you calculate the adjusted running total, which uses the previously calculated total to May 1-17, but ignores the previous total after May 1-17 and instead sums the [Debit / Credit] column from May 1 to May 17, 17 total plus 20 . (addendum 20, because we know that the total amount is 20 on May 1 -17, and this will not be reflected in the sum of the [Debit / Credit] column.)
Running Total Is Capped = IF([Annual Leave Column2] > 20, 1, 0) Running Count of Capped = CALCULATE ( SUM ( Sheet1[Running Total Is Capped] ), ALL ( Sheet1 ), FILTER ( Sheet1, Sheet1[Date] <= EARLIER ( Sheet1[Date] ) ) ) Adjusted Running Total = IF ( [Running Count of Capped] = 0, [Annual Leave Column2], 20 + CALCULATE ( SUM ( Sheet1[Debit/Credit] ), ALL ( Sheet1 ), FILTER ( Sheet1, Sheet1[Date] <= EARLIER ( Sheet1[Date] ) && Sheet1[Running Count of Capped] > 1 ) ) )
This solution is not delayed, because it only works the first time you click the header. Each time you get into the cap, you will need to adjust the total amount in the same way, using a new set of calculated columns that adjusts the adjusted total. If you can wear a hat 20 times, or 50 times, you will need the aforementioned set of calculated columns, repeating 20 or 50 times.
You cannot adjust the cap for all rows at once, because the first setting affects the subsequent setting. In your example data, the true total amount for August 5 -17 is 21 years, which means that we want to reduce it to 20. However, since we have crossed the cap three times already, we have already shaved 3.5 days from the final result as a result, and therefore, the adjusted total is 17.5 and therefore does not require a limitation.
Besides the pure amount of calculated columns that you need, the model will also not be well dependent on the increase in data volume. The EARLIER function is iterative, which means that it computes once for each row. The more lines, the longer it takes. Using the EARLIER function over and over again as this quick and dirty workaround makes the performance killer. I highly recommend finding another solution before the data reaches Power BI.
Sidenote: if you intend to use the EARLIER function, I would recommend indexing each row so that they have a unique number, and not on the date field as an index. Using a date field as an index can lead to unexpected results if you have several credits / debits on the same date.