Rolling Linear Regression Lookback Comparisons

[schwiggiddy]

I have a dataset that includes the day of the sample data, the result for that day, and using Power BI, I've calculated a linear regression formula based on these two columns.

Here's the rub. I'm looking to create a rolling linear regression. Out of the 100 or so days in the set, what's the linear regression for the previous 10 days, for example, but for each day. Simple enough, but now, for each day, what is the difference between the calculated linear regression line from that day's data, and what is the largest difference of all the 10 days, for each day?

So for day 100, the regression formula is a y=mx+b expression with m and b in separate columns. I think I need to plug in the x and determine the y for each of the previous 10 days' values and take the max and min of that set of numbers to find the furthest values from the regression line over the previous 10 days (which days do not matter). Then repeat for day 99, looking back on the 10 days prior to that with m and b values unique to that day, and repeat for each row of data to have a rolling max deviation from the regression line.

Is there a function like List.Range that would allow each row to have these operations made to it?

 

[schwiggiddy]

I've attempted my own custom function to do (I think) what I want it to do, but I'm getting an error. Here's the function:

(close as any, slopeb as number, intercepta as number, index as number, antiindex as number) =>
let
    Source = Table.FillDown(
        Table.FromColumns({close,slopeb,intercepta,index,antiindex},{"Close", "Slopeb","Intercepta","Index","AntiIndex"})
        ),
    AddY = Table.AddColumn(Source, "YValue", each [Intercepta] + [Index] * [Slopeb]),
    SubtractClose = Table.AddColumn(AddY, "Delta", each [YValue] - [Close]),
    MaxDelta = Table.AddColumn(SubtractClose, "Max", each Table.Max(SubtractClose[Delta])),
    MinDelta = Table.AddColumn(MaxDelta, "Min", each Table.Min(MaxDelta[Delta])),
    FirstRows = Table.FirstN(MinDelta, 1)
in
    FirstRows

close gets a list passed to it, each of the other variables are a single number, hence nesting it in the FillDown operation. The error is
Expression.Error: We cannot convert the value <the value in slopeb> to type List. If anything I thought I'd have to convert close to a table somehow.

 

[schwiggiddy]

Progress...progress...
New error, cannot convert type table to type list. Wish it would tell me where there was an issue.

(close as any, slopeb as number, intercepta as number, index as number, antiindex as number) =>
let
    listslopeb = {slopeb},
    listintercepta = {intercepta},
    listindex = {index},
    listantiindex = {antiindex},
    Source = Table.FillDown(
        Table.FromColumns({close,listslopeb,listintercepta,listindex,listantiindex},{"Close", "Slopeb","Intercepta","Index","AntiIndex"}), {"Slopeb","Intercepta","Index","AntiIndex"}
        ),
    AddY = Table.AddColumn(Source, "YValue", each [Intercepta] + [Index] * [Slopeb]),
    SubtractClose = Table.AddColumn(AddY, "Delta", each [YValue] - [Close]),
    MaxDelta = Table.AddColumn(SubtractClose, "Max", each List.Max(SubtractClose[Delta])),
    MinDelta = Table.AddColumn(MaxDelta, "Min", each List.Min(SubtractClose[Delta])),
    BuildTable = Table.FromList(MinDelta),
    Return = Table.AddColumn(BuildTable, "MaxDelta", each Table.FromList(MaxDelta))
in
    Return