Running total to retain previous row total instead of resetting to zero when a specific condition is met

I want to achieve the following calculation highlighted in the Desired Output column:

highlighted row has a running total of 6.4 M, that exceeds the 5 M hard limit so the SQL should return the previous row value 3.64M
(thus in Excel the formula would be I9=IF(I8+F9 > $H$2, I8, I8+F9))
same logic should happen for the following rows when ever the Running total breaches the Hard limit

• F. Sale amount
• G. Running Total : Running total of Sale amount
• H. Hard Limit : Hard limit when running total exceeds this value the previous row running total should be considered
• I. ultimate output

request you to please help me out with Ideas to achieve this.

Your best bet will be to use a recursive Common Table Expression.

I voluntarily chose the simplest use (row-by-row iterations), because it will be a lot to learn at first,
but you will then be able to optimize it (for example by using a windowed sum() over () to make a first pass containing "everything surely under the cutoff" instead of the row-by-row iterations),
or use RDBMS specific functions (PostgreSQL User-Defined Aggregates).

Here is it with PostgreSQL (you didn't tell which RDBMS you use, so I choose the one I prefer…):

with recursive
-- Give each row its position, we'll need it to iterate from row to row:
p as (select row_number() over() pos, * from t),
-- Now "recurse" (iterate) over rows:
r as
(
  -- Init our recursion with the first amount, use its amount as the first value of the running sum, and as the capped sum if it's less than the cap:
  select *, amount rs, case when amount > 5000000 then 0 else amount end capped from p where pos = 1
  -- Now the recursive part:
  union all
  select p.*, rs + p.amount, capped + case when capped + p.amount > 5000000 then 0 else p.amount end
  -- Use r itself (the previous iteration of our recursive CTE) to have the pos of the last iteration, and fetch the row having the next pos
  from r join p on p.pos = r.pos + 1
)
select * from r order by pos;

I've put it running on your data in an example fiddle you can play with.