Forecasting

[SamJohnson]

I am having an issue with my forecasting model involving a large purchase of stores a few years ago inflatting the totals sales and causing my model to predict another large jump every 4 years. I have tried smoothing the model and it still shows large increases in sales in the future. Any and all help is greatly appreciated.

[v-prasare]

Hi @SamJohnson,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-dineshya · Accepted solution]

Hi @SamJohnson ,

Thank you for reaching out to the Microsoft Community Forum.

The model is a cyclical pattern caused by a one-time event the large store purchase and is incorrectly projecting similar spikes every 4 years due to the seasonality settings.

You can address this without compromising the integrity of your data.

1. Since the seasonality is set to 4 points likely auto-detected, try manually settings it to a value that reflects your actual business cycle like yearly or quarterly rather than letting the model infer it from the spike.

2. Flag the expansion year as an outlier and exclude it from the forecast input or adjust the sales data for that year to reflect normalized growth like average growth rate from surrounding years.

Note: This can be done by creating a separate column with adjusted values and using that for forecasting.

3. Split your data into Organic growth (excluding expansion years) and Expansion impact (separate analysis)

Note: Forecast only the organic growth and then layer in expected expansion effects manually if needed.

4. Switch to DAX for Custom Forecasting. By using DAX you can build a more flexible model that detects and adjusts for structural breaks. Applies custom smoothing or regression techniques.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @SamJohnson ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @SamJohnson ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[raji_n]

@SamJohnson if I am an analyst I wouldnt rely completely on Power BI forecasting, it might not work for all cases. 

Thanks

[SamJohnson]

I am curently using the built in forecasting model without DAX. Below are the settings I have for the model:

My question is, is there a way to eliminate the pricing increase due to expansion without comprimising the data?

[Gabry]

I think there isn’t much you can do with the forecasting options alone. The only thing you can do is set up the 'ignore last' option if the compromised years are the most recent ones.

[raji_n]

Hi @SamJohnson , please provide more detail on what is done so far so that I can help.

[Gabry]

Hey Sam,

wich strategy are you using to calculate the forecast?
You made some dax or you just used the forecast from the line chart?
Please provide more detail.
Anyway one possibile solution could be to use moving average, or exclude periods with exceptionally high / low volume.