Linear Forecasting

[jacquelineneo14]

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 · Accepted solution]

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. 

[jacquelineneo14]

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]

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

[lbendlin]

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.