How to Forecast in Power BI

Predicting future trends from your historical data doesn't require a crystal ball - just Power BI's native forecasting feature. This powerful yet simple tool uses built-in models to project future values for things like sales, website traffic, or inventory levels. This guide will walk you through exactly how to prepare your data, create a forecast in a few clicks, and interpret the results to make smarter business decisions.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly is Forecasting?

In the context of data analysis, forecasting is the process of using historical data to make informed predictions about the future. It’s not about guessing, it's about identifying patterns, trends, and seasonality in your past data to project what is most likely to happen next.

Power BI's forecasting function is based on a standard statistical method called Exponential Smoothing (ETS). Without getting too technical, this method is excellent for time-series data because it gives more weight to recent data points while still accounting for overall trends and seasonal patterns. This makes it a dependable choice for many common business forecasting needs.

When Should You Use Forecasting in Power BI?

Forecasting is most useful when you have a track record of data over time and want to anticipate future performance. It helps you move from reactive analysis (what happened?) to proactive planning (what’s likely to happen?).

Here are a few practical examples:

• Sales Forecasting: Predict sales revenue for the next quarter to set realistic targets for your team.
• Inventory Management: Estimate future demand for a product so you know how much stock to order, avoiding both shortages and overstock situations.
• Resource Planning: Forecast support ticket volume to ensure you have enough staff scheduled for the busy season.
• Website Traffic Projections: Estimate the number of users you can expect on your site next month to inform server capacity and marketing goals.
• Budgeting: Project future expenses or revenue to create a more accurate annual budget.

Preparing Your Data for an Accurate Forecast

The quality of your forecast is entirely dependent on the quality of your data. The saying "garbage in, garbage out" is especially true here. Before you even think about clicking the forecast button, make sure your data meets a few key requirements.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

1. Your Data Must Be a Time-Series

At its core, forecasting analyzes change over time. This means your dataset must have a date or time component. This could be daily, weekly, monthly, or yearly data. Power BI needs this continuous timeline to identify trends and patterns.

2. Ensure Consistent Time Intervals

For the best results, your data points should be recorded at consistent intervals. If you’re analyzing monthly sales, you should have one data point for each month. If your data is recorded daily but some days are missing, the ETS algorithm can handle small gaps, but large, irregular gaps can throw off the model and lead to inaccurate predictions.

3. Have Enough Historical Data

You can’t predict a year's worth of sales based on one month of data. Power BI needs a sufficient amount of historical information to reliably detect patterns. A general rule of thumb is to have at least two full seasonal cycles of data. For example, if you have a yearly sales cycle (e.g., busy holidays, slow summers), you should provide at least two years of data for the model to work effectively.

Step-by-Step Guide: Creating a Forecast in Power BI

Once you’ve confirmed your data is ready, creating a forecast visual is surprisingly simple. We’ll use a sample dataset of monthly sales to walk through the process.

Step 1: Create a Line Chart Visual

First, add a Line chart to your Power BI report canvas. A line chart is the only native visual that supports the built-in forecasting feature, as it's the natural choice for visualizing data over time.

Step 2: Add Your Data to the Visual

Next, populate the line chart with your data:

• Drag your date field (e.g., 'OrderDate') into the Axis well.
• Drag the numerical value you want to forecast (e.g., 'Sales') into the Values well.

You should now see a line chart displaying your historical sales data over time.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Access the Analytics Pane

With the line chart visual selected, look at the Visualizations pane on the right. Click on the magnifying glass icon to open the Analytics pane. This is where you’ll find advanced analytical features, including forecasting.

Step 4: Add and Configure Your Forecast

In the Analytics pane, scroll down until you see the Forecast section. Click the drop-down arrow to expand it, then click + Add.

Just like that, Power BI will automatically analyze your data and add a forecast to your line chart!

You’ll notice three new things on your chart:

1. The Forecast Line: This darker line extends beyond your actual data, showing the projected values.
2. The Upper Bound: The top line of the shaded area, representing the optimistic forecast scenarios.
3. The Lower Bound: The bottom line of the shaded area, representing the pessimistic forecast scenarios.

This shaded area is the confidence interval, which we’ll discuss next.

Understanding the Forecasting Parameters

While the automatic forecast is a great start, you can fine-tune it for greater accuracy and relevance. The options available in the Forecast section of the Analytics pane give you more control.

Forecast length

This determines how far into the future you want to predict. You can set the unit to Points, Days, Months, Quarters, Years, etc., depending on the granularity of your date axis. Be careful not to forecast too far, predictions become less reliable the further they are from known data.

Ignore last

This is a handy feature for excluding incomplete, recent periods. For example, if you are looking at data on the 10th of July, the data for July is not yet complete. Including this partial month could skew the trend. Setting "Ignore last" to "1" Point (or "1 Month") will exclude that incomplete period from the calculation.

Confidence interval

This setting controls the width of the shaded area around your forecast line. In simple terms, a 95% confidence interval means Power BI is 95% confident that the actual future values will fall within this range. Increasing it to 99% will widen the range (more certainty, but less precise), while decreasing it to 90% will narrow it (more precise, but less certain).

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Seasonality

This is arguably the most important parameter to get right. Seasonality refers to predictable, repeating patterns in your data that occur at regular intervals. A clothing retailer sees a sales spike every winter, and an ice cream shop sees one every summer - that's seasonality.

By default, Power BI tries to autodetect the seasonal cycle. However, if you know your business cycle, it’s always better to set it manually. You need to tell Power BI how many data points make up one full seasonal cycle. Here are common examples:

• For monthly data that has a yearly pattern, set Seasonality to 12.
• For quarterly data with a yearly pattern, set it to 4.
• For daily data with a weekly pattern, set it to 7.

Getting this right can dramatically improve the accuracy of your forecast.

Limitations of Power BI's Built-in Forecasting

Power BI's forecasting is excellent for quick, accessible predictions, but it's important to understand its limitations:

• Single Algorithm: It only uses the ETS method. While versatile, ETS isn't the best fit for every type of dataset. More complex scenarios may require different models.
• Univariate Analysis Only: The forecast for a value (like sales) is based only on its own historical data. It can't consider other variables that might influence it, such as marketing spend, competitor campaigns, or inventory levels.
• Not for Advanced Statistical Modeling: If you need to incorporate multiple variables or more sophisticated models, you would need to integrate R or Python scripts into your Power BI report, which is a much more advanced topic.

Final Thoughts

Creating forecasts in Power BI is a straightforward way to transform historical data into forward-looking insights. By ensuring your data is clean and properly formatted and by fine-tuning key parameters like seasonality, you can generate valuable projections that aid in planning and strategy across your entire business.

While Power BI helps you build powerful visuals, navigating the setup and configuration still has a steep learning curve. We built Graphed because we believe getting data shouldn't be that hard. Instead of clicking through menus and dragging fields, you can simply ask, "Compare our Facebook ad spend versus Shopify revenue by campaign for the last 90 days." Graphed connects to your live data sources and builds an interactive dashboard for you in seconds, turning hours of manual report building into simple, conversational questions.