How to Correctly Apply Limits on the Result in DAX (and SQL)

Calculating a running total with a maximum cap, such as employee overtime, presents a tricky challenge in Power BI and SQL. A simple DAX formula that caps the total fails because it doesn't use the corrected value from the previous period in the next period's calculation, leading to incorrect results. This type of iterative, row-by-row logic is difficult in DAX, as it creates a circular dependency that the language is not designed to handle. The most robust solution is to perform this procedural calculation in the data source itself, using SQL with a cursor to process each record sequentially. This approach allows you to check the corrected balance from the previous month before adding the current month's value and reapplying the cap.