Quarter Linear Regression

[FilipeSantos_pt]

Hi,

I am trying to create in PowerBi a QUARTERLY linear trend line that exists as a graphic element in excel, .

I tried the following DAX formula:

linearReg = 

VAR Known =

FILTER (

SELECTCOLUMNS (

ALLSELECTED ( Dates[Date] ),

"Known[X]", Dates[Date],

"Known[Y]", [Quantity]

),

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

SUMX(

DISTINCT ( Dates[Date] ),

Intercept + Slope * Dates[Date]

)

I followed the examples of the following links:
https://xxlbi.com/blog/simple-linear-regression-in-dax/
https://community.powerbi.com/t5/Desktop/Linear-Regression-to-predict-values/m-p/747142
https://www.youtube.com/watch?v=d1kHgFULcko 

I am using a Date Dimension table that has a 1-* relation with table quantities that has two colums, date and quantity.

At this point I used on the x-axis a Quarter/year column from my date dimension.

However the result that I am obtaining is very far off from the values and the trend line of excel.

I already tried to use the quarter column from the date dimension, tried ignoring the date dimension table and only use the date from quantities table without the relationship. Also I tried to group the quantities table by year/quarter with no result

Can anyone help me? 

Thank you in advance

[FilipeSantos_pt · Accepted solution]

Alberto Ferrari from SQLBi posted in a comment on one of his videos the following link:

Simple Linear Regression in DAX with Hierarchy on Date Table – Sergio Murru

I hope that this helps other people that, like me, needed something more "substantial" as an answer.

[v-easonf-msft]

Hi, @FilipeSantos_pt 

If possible, can you share some sample data and expected result in excel  for further research?

Best Regards,
Community Support Team _ Eason