Forum Discussion

Anonymous

Not applicable

7 years ago

Linear Regression to predict values

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 = ...

Community Support

7 years ago

hi, Anonymous

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

Anonymous
Not applicable
7 years ago
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

Anonymous

Not applicable

7 years ago

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