topic Re: Linear Forecasting in DAX Commands and Tips

Linear Forecasting

jacquelineneo14 — Fri, 14 Feb 2025 01:40:12 GMT

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!

Re: Linear Forecasting

lbendlin — Fri, 14 Feb 2025 01:49:16 GMT

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.

Re: Linear Forecasting

jacquelineneo14 — Fri, 14 Feb 2025 02:30:36 GMT

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?

Re: Linear Forecasting

lbendlin — Fri, 14 Feb 2025 02:32:53 GMT

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

Re: Linear Forecasting

Anonymous — Mon, 17 Feb 2025 06:51:27 GMT

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.