Forum Discussion

Proquation's avatar
Proquation
Frequent Visitor
2 years ago
Solved

Confusion with Linear Regression (DAX)

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 imp...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    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

     

AlexisOlson's avatar
AlexisOlson
Super User
2 years ago

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