Forum Discussion

jacquelineneo14's avatar
jacquelineneo14
New Member
1 year ago
Solved

Linear Forecasting

Hi,

I want to forecast linearly using the average nett hire for the next 10 years or so. However, i am faced with an issue.

I would like to find out why after Feb 2526 my data does not continue to forecast using the average nett hire as shown from the image below.

Would be great if anyone can help me on this.

 

Thank you!

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi jacquelineneo14 ,

     

    Thanks lbendlin for the quick reply and solution. I have some other ideas to add:

    1. Calculate the Average Net Hire:

    AverageNetHire = AVERAGE(Table[NetHire])

    2. Create a Date Table:
    Ensure you have a date table that covers the forecast period.

    3. Calculate the Forecast:

    Forecast = 
VAR LastHistoricalDate = MAX(Table[Date])
VAR ForecastPeriod = 120 // 10 years * 12 months
RETURN
IF(
    Table[Date] > LastHistoricalDate,
    [AverageNetHire] * (DATEDIFF(LastHistoricalDate, Table[Date], MONTH) + 1),
    BLANK()
)

     

    For more details, please refer:

    Implementing linear regression in Power BI - SQLBI

    Adding Trend Lines & Forecasts in Power BI - GeeksforGeeks

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Any reason for not using LINESTX()  ?

     

    To report on things that are not there ( for example future dates) you may need to use disconnected tables.

  • jacquelineneo14's avatar
    jacquelineneo14
    New Member
    1 year ago

    Thanks for replying me. I did not use LINEXTX() because I am only using 2024 and thought of using the average nett hire, has some difficulties calculating the slope and intercept.

     

    What do you mean by disconnected tables?

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      Those are tables that are not connected to any other tables in the data model.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi jacquelineneo14 ,

     

    Thanks lbendlin for the quick reply and solution. I have some other ideas to add:

    1. Calculate the Average Net Hire:

    AverageNetHire = AVERAGE(Table[NetHire])

    2. Create a Date Table:
    Ensure you have a date table that covers the forecast period.

    3. Calculate the Forecast:

    Forecast = 
VAR LastHistoricalDate = MAX(Table[Date])
VAR ForecastPeriod = 120 // 10 years * 12 months
RETURN
IF(
    Table[Date] > LastHistoricalDate,
    [AverageNetHire] * (DATEDIFF(LastHistoricalDate, Table[Date], MONTH) + 1),
    BLANK()
)

     

    For more details, please refer:

    Implementing linear regression in Power BI - SQLBI

    Adding Trend Lines & Forecasts in Power BI - GeeksforGeeks

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.