Linear Regression to predict values

[ioper]

Hello, 

I am trying to predict future values based on historical data. The new values are displayed in a matrix visual with future dates as the rows. Here is my code: 

linReg = 
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED (' Claims This Year'[ Date] ),
            "Known[X]", 'Claims This Year'[ Date],
            "Known[Y]", [totalAmount]
        ),
        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 + Slope * [monthNumber]

I have a feeling it has something to do with the ALL SELECTED part because the historical dates are not in the visual but I am not sure. 

Also, please advise on how to counter blanks in the historical data matrix (Dates = rows, type = columns, claimTotal= values) being counted as nulls instead of 0. This is changing my slope. 

Thank you!!

[v-lili6-msft]

hi, @ioper 

Use ALLSELECTED, it will only calculate the date that has been filtered.

and for being counted as nulls instead of 0, if you could try to filter out blank value in the calculation.

and if not your case, Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Lin

[ioper]

@v-lili6-msft 

This data is summary data (a visual of sums made from the raw data

	MonthNo	Car	Train	Tractor
Jan-17	1	1	3	4
Feb-17	2	2	6	5
Mar-17	3	3	5	6
Apr-17	4	4	3	8
May-17	5	5	6	7
Jun-17	6	6	5	9
Jul-17	7	4	9	15
Aug-17	8	8	12	4
Sep-17	9	9	14	2
Oct-17	10	2	15	36
Nov-17	11	1	16	5
Dec-17	12	2	18	4
Jan-18	13	9	20	8
Feb-18	14	10	20	56
Mar-18	15	11	21	5
Apr-18	16	12		8
May-18	17	13		24
Jun-18	18	14	28	13
Jul-18	19	16	30	25
Aug-18	20	15	25	6
Sep-18	21	14	21	25
Oct-18	22	15	14	36
Nov-18	23	16	31	36
Dec-18	24	17	35	18
Jan-19	25	1	36	19
Feb-19	26		37	20
Mar-19	27	18	40	40
Apr-19	28	19	42	41
May-19	29	21	42	34
Jun-19	30	22	19	40

** Ideally, those blanks should be counted as 0 if there is no data. 

Now, my goal is to find the slope and intercept of each column so that I can predict sales for the rest of the year. 

so that I would have

	MonthNo	Car	Train	Tractor
Jul-19	31	=(SLope * monthNo) + intercept	=(SLope * monthNo) + intercept	=(SLope * monthNo) + intercept
Aug-19	32	=(SLope * monthNo) + intercept	=(SLope * monthNo) + intercept	=(SLope * monthNo) + intercept
Sep-19	33	=(SLope * monthNo) + intercept	=(SLope * monthNo) + intercept	=(SLope * monthNo) + intercept
Oct-19	34	=(SLope * monthNo) + intercept	=(SLope * monthNo) + intercept	=(SLope * monthNo) + intercept
Nov-19	35	=(SLope * monthNo) + intercept	=(SLope * monthNo) + intercept	=(SLope * monthNo) + intercept
Dec-19	36	=(SLope * monthNo) + intercept	=(SLope * monthNo) + intercept	=(SLope * monthNo) + intercept

[ioper]

@v-lili6-msft 

ALLSELECTED is currently being used, but the result is still incorrect. Is there anywhere else that the code needs to be changed?

Also with the 0 sitution, I want them to be counted as 0 and not null values (as it would in excel). Please advise