ByRow function produces two different results depending on whether row is range or array

Viz 

I followed JamesDuley 's link and found a related question from a personal friend of some 20 years standing.  In answering his question, I advanced my own understanding of the problem

TEXTSPLIT combined with BYROW returns an unexpected result when using - Microsoft Community

My conclusions were that BYROW is designed to accept a 2D matrix and pass it to the calculation, row at a time.  In the special case of the matrix having only a single column, BYROW will pass single values to the calculation, but they will still be

'Type = 64' and will get caught by any (monumentally undesirable) legacy nested array checks.  Passing the single value (1x1) array can be made to work if you always reference its value using '@', in order to convert it to a scalar value.

Where BYROW references a Range, things proceed somewhat differently.  In normal use BYROW passes row ranges to the calculation engine (COUNTIFS will work with such an object as its criterion range for example) but, in the special case, the row might reduce to a single cell reference. 

= TYPE(cell)

will then return a 1 or 2, so the calculation proceeds differently depending on whether the initial BYROW reference is an array or a range object.

Viz 

SergeiBaklan seems to have got to the bottom of the problem.  Yet another example of the failure of the Calc engine to address the problem of arrays of arrays (that is, 'normal' problems).  

I do not have much extra to offer; I was interested in trying to master the new AFE.  I decided that the problem is better suited to MAP, so bypassed the problem rather than addressing it.  Rather than showing the anonymous Lambda function on the worksheet, I defined a Named Lambda 'AverageLast7λ' in its Curried form, giving the worksheet formula

=LET(
    Values,  TAKE(SORT(Data), , -1),
    Sqn,     SEQUENCE(ROWS(Values)),
    MAP(Sqn, AverageLast7λ(Values))
);

AverageLast7λ
= LAMBDA(v, LAMBDA(a,
    IF(a<7,"NA", 
        AVERAGE(INDEX(v, SEQUENCE(7, 1, a,-1)))
    )
  ));

The SORT and AVERAGE formulas are different, but it is simply change for change's sake.

Viz 

BYROW() assumes you transfer an array as parameter, even if that array has only one element. SEQUENCE() from array works differently, you may compare =SEQUENCE(2) and =SEQUENCE({2}).

To fix, you may pass first element of each row array

=LET(
    Values, SORTBY(Data[S&P BSE Sensex], Data[DATE]),
    Sqn, SEQUENCE(ROWS(Values)),
    BYROW(
        Sqn,
        LAMBDA(a,
            IF(
                @a < 7,
                "NA",
                AVERAGE(
                    INDEX(Values, SEQUENCE(7, 1, @a - 7 + 1))
                )
            )
        )
    )
)

 or simply change BYROW on MAP

=LET(
    Values, SORTBY(Data[S&P BSE Sensex], Data[DATE]),
    Sqn, SEQUENCE(ROWS(Values)),
    MAP(
        Sqn,
        LAMBDA(a,
            IF(
                a < 7,
                "NA",
                AVERAGE(
                    CHOOSEROWS(
                        Values,
                        SEQUENCE(7, 1, +a - 7 + 1)
                    )
                )
            )
        )
    )
)

which passes to lambda value, not array.

I don't have an answer why right now but your 'wrong' answer are just your original values NOT averaged offset by 7. That is probably a good clue what is happening.

One more clue: This variation also works:

=LET(Values, SORTBY(Data[S&P BSE Sensex],Data[DATE]),
     Sqn, SEQUENCE(ROWS(Values)),
     Seven, SEQUENCE(7),
     BYROW(Sqn, LAMBDA(a,
         IF(a<7,"NA", AVERAGE(INDEX(Values, a-7+Seven))))))

Basically setting up the {1,2,3,4,5,6,7} array ahead of time and then simple math inside