Forum Discussion
Predictions in Power BI
Hi community,
I'm working with monthly data spanning 25 months in this structure:
month | category1 | category2 | category3 | total_customers
Hi MattiaFratello , You can make use of the line chart and the default forecast options .
Data :
Line chart :
Setting :
if you need to build a linear regression you can build it using the dax but check before hand whether the data fits the line .
Linear Forecast =VAR _HistoricalData = FILTER( ALL('CustomerData'), NOT ISBLANK('CustomerData'[Total_Customers]) )
VAR _N = COUNTROWS(_HistoricalData)
VAR _SumX = SUMX(_HistoricalData, INT('CustomerData'[Month]))
VAR _SumY = SUMX(_HistoricalData, 'CustomerData'[Total_Customers])
VAR _SumXY = SUMX(_HistoricalData, INT('CustomerData'[Month]) * 'CustomerData'[Total_Customers])
VAR _SumX2 = SUMX(_HistoricalData, INT('CustomerData'[Month])^2)
// m and b
VAR _Slope = DIVIDE( (_N * _SumXY) - (_SumX * _SumY), (_N * _SumX2) - (_SumX * _SumX) )
VAR _Intercept = DIVIDE( _SumY - (_Slope * _SumX), _N )
// Forecast period
VAR _CurrentDate = INT(MAX('FutureCalendar'[Date]))
// y = mx + b
VAR _Prediction = (_Slope * _CurrentDate) + _Intercept
RETURN
_Prediction
Thanks
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster
1 Reply
- Natarajan_MSuper User
Hi MattiaFratello , You can make use of the line chart and the default forecast options .
Data :
Line chart :
Setting :
if you need to build a linear regression you can build it using the dax but check before hand whether the data fits the line .
Linear Forecast =VAR _HistoricalData = FILTER( ALL('CustomerData'), NOT ISBLANK('CustomerData'[Total_Customers]) )
VAR _N = COUNTROWS(_HistoricalData)
VAR _SumX = SUMX(_HistoricalData, INT('CustomerData'[Month]))
VAR _SumY = SUMX(_HistoricalData, 'CustomerData'[Total_Customers])
VAR _SumXY = SUMX(_HistoricalData, INT('CustomerData'[Month]) * 'CustomerData'[Total_Customers])
VAR _SumX2 = SUMX(_HistoricalData, INT('CustomerData'[Month])^2)
// m and b
VAR _Slope = DIVIDE( (_N * _SumXY) - (_SumX * _SumY), (_N * _SumX2) - (_SumX * _SumX) )
VAR _Intercept = DIVIDE( _SumY - (_Slope * _SumX), _N )
// Forecast period
VAR _CurrentDate = INT(MAX('FutureCalendar'[Date]))
// y = mx + b
VAR _Prediction = (_Slope * _CurrentDate) + _Intercept
RETURN
_Prediction
Thanks
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster