Calculate Alpha and Beta of a Linear Regression using DAX

[datacauts]

Hello! I'm stuck in creating a measure that simulates a linear regression.

Basicaly I have two variables, benchmark (X) and stocks (Y). What I need to do is calculate the Alpha and Beta of a linear regression between them for the selected period. I tried to used the following structure:

alfa =

VAR Known =

    FILTER (

        SELECTCOLUMNS (

            ALLSELECTED ( mydata[date] ),

            "Known[X]", SUM(mydata[benchmark_x]),

            "Known[Y]", SUM(mydata[stock_allocation])

        ),

        AND (

            NOT ( ISBLANK ( Known[X] ) ),

            NOT ( ISBLANK ( Known[Y] ) )

        )

    )

VAR Count_Items =

    COUNTROWS ( Known )

VAR Sum_X =

    SUMX ( Known, Known[X] )

VAR Sum_X2 =

    SUMX ( Known, Known[X] ^ 2 )

VAR Sum_Y =

    SUMX ( Known, Known[Y] )

VAR Sum_XY =

    SUMX ( Known, Known[X] * Known[Y] )

VAR Average_X =

    AVERAGEX ( Known, Known[X] )

VAR Average_Y =

    AVERAGEX ( Known, Known[Y] )

VAR Slope =

    DIVIDE (

        Count_Items * Sum_XY - Sum_X * Sum_Y,

        Count_Items * Sum_X2 - Sum_X ^ 2

    )

VAR Intercept =

    Average_Y - Slope * Average_X

RETURN

Intercept

where Alpha = Incercept and Beta = Slope

but the results doesn't match the correct expected values because DAX is just calculating the measure for every row/date in the series and summing all of them in the end. That's not what I want (editado)

Can someone help me with that? Will share the file here

https://file.io/oc2IuTkSHqZY

the expected value for Alpha from 2002 to 2022 (no filters on Date) is 0,74. I achieve the expected results via Calculated columns, but they don't change with filters so It's not very useful for me

[Greg_Deckler]

@datacauts Have you reviewed this? Simple Linear Regression - Microsoft Power BI Community

And this? De-Seasonalized Correlation Coefficient - Microsoft Power BI Community