SQL lag function avoid getting first row as null

I have a sql query

    SELECT VALUE, LAG(VALUE,1,null) over(order by LOAD_DATE asc) as PREV_VALUE 
FROM TABLE LOAD_DATE >some_date

LOAD_DATE | VALUE
20210625    25
20210626    27
20210627    null
20210628    29

if my filter date is > 20210625 , I get value as 27 and previous value as null , is it possible to get the prev_value 25. If there is no filter I will get it. But filter is required to prevent reading the whole table. Also one more requirement. If previous value is null for example , date – 20210628, I should be getting previous non-null value that is 27.

Answer

An option would be to calculate the last known value from the table and use it as a default value in LAG:

DECLARE @Test TABLE (LOAD_DATE DATE, [VALUE] INT);

INSERT INTO @Test (LOAD_DATE, [VALUE])
VALUES
        ('2021-06-25', 25)
    ,   ('2021-06-26', 27)
    ,   ('2021-06-27', null)
    ,   ('2021-06-28', 29)
;

DECLARE @LoadDate DATE = '2021-06-24';

WITH LastValue AS (
    SELECT
        MAX([VALUE]) AS [VALUE] /* This is to make sure, there is at least one record returned (e.g. the date is earlier than the first record in the table. */
    FROM
        (
            SELECT TOP(1)
                [VALUE]
            FROM
                @Test
            WHERE
                LOAD_DATE <= @LoadDate
            ORDER BY
                LOAD_DATE DESC
        ) X
)
SELECT
        T.*
    ,   LAG(T.VALUE, 1, LV.[VALUE]) OVER (ORDER BY T.LOAD_DATE) AS PREV_VALUE
FROM
    @Test T
    CROSS JOIN LastValue LV
WHERE
    T.LOAD_DATE > @LoadDate
;