Confusion with Linear Regression (DAX)

[Proquation]

Hi there,

I have a railway dataset for prices per day.

I'm having some trouble with understanding simple linear regression using DAX. I understand the formula being used and I've tried to implement it:

Here's what I get when I plot the Price vs. Date as well as the Regression Line. As you can see, it is not a straight line as simple linear regression should be.

Another issue is that it takes the sum of prices per day instead of the average, which is what I want to predict the average prices per day. 

How can I fix this issue?

I've attached the pbix file below:

railway_data.pbix 

[AlexisOlson · Accepted solution]

You can do this manually with those formulas, but I'd recommend LINESTX instead.

Either way, you need to aggregate by date before doing your regression.

VAR _Data_ =
    SUMMARIZE (
        ALLSELECTED ( railway ),
        railway[Date of Purchase],
        "@SumPrice", SUM ( railway[Price] )
    )
VAR _Regression_ =
    LINESTX ( _Data_, [@SumPrice], railway[Date of Purchase] )
VAR _Slope     = SELECTCOLUMNS ( _Regression_, [Slope1] )
VAR _Intercept = SELECTCOLUMNS ( _Regression_, [Intercept] )
VAR _Date      = MAX ( railway[Date of Purchase] )
VAR _Result =
    IF ( NOT ISEMPTY ( railway ), _Date * _Slope + _Intercept )
RETURN
    _Result