Forum Discussion
Linear Regression to predict values
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
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 |