t, Sql Server - rextester

Language: Layout:

DECLARE @test TABLE
(
ID INT IDENTITY (1,1)
,PeriodDate DATE
,X INT
,Y INT
,DesiredResult INT
,DesiredResultExplanation VARCHAR(100)
)

INSERT INTO @test VALUES ('2017-05-01', 100,0, 100,'Nothing left to subtract.  Value is unchanged')
INSERT INTO @test VALUES ('2017-05-08', 200,0, 200,'Nothing left to subtract.  Value is unchanged')
INSERT INTO @test VALUES ('2017-05-15', 300,0, 100,'300 - 200 = 100  (Orig -1100 has been consumed)')
INSERT INTO @test VALUES ('2017-05-22', 400,0,-200,'400 - 600 = -200 ')
INSERT INTO @test VALUES ('2017-05-29', 500,-1100,-600, '500 - 1100 = -600')

SELECT *
FROM @test
ORDER BY PeriodDate DESC;

WITH cte as (
    SELECT *,
           SUM(X) OVER (ORDER BY PeriodDate DESC) accumulated   
    FROM @test
), parameter as (
    SELECT 1100 as startY
)    
SELECT *,
       CASE WHEN accumulated <= startY 
            THEN accumulated - startY
            WHEN LAG(accumulated) OVER (ORDER BY PeriodDate DESC) < startY
            THEN accumulated - startY
            ELSE X
       END as newDesire
FROM cte
CROSS JOIN parameter
ORDER BY PeriodDate DESC;

View schema
Execution time: 0,02 sec, rows selected: 10, rows affected: 5, absolute service time: 0,16 sec