Forum Discussion

MattiaFratello's avatar
MattiaFratello
Super User
6 months ago
Solved

Predictions in Power BI

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?
  • 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

  • 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