Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
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
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 |
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |