Predictions in Power BI

[MattiaFratello]

Hi community,

I'm working with monthly data spanning 25 months in this structure:

month | category1 | category2 | category3 | total_customers

 

My goal is to generate predictions for 3 future months based on historical trends.

Even better if I can appen them to the existing table.

 

What's the best approach?

[Natarajan_M · Accepted solution]

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