Graphed LogoGraphed
Login

How to Make a Time Series Plot in Power BI

Cody Schneider•

Tracking your metrics over time is fundamental to understanding your business's health. Whether it's website traffic, sales figures, or ad performance, you need to see how these numbers change to find trends and make informed decisions. This article will walk you through exactly how to create a time series plot using a line chart in Power BI, from setting up your data to adding advanced analytical features.

What is a Time Series Plot?

In simple terms, a time series plot is a chart that shows data points arranged in chronological order. Usually, this means using a line chart where the horizontal (X-axis) represents time, and the vertical (Y-axis) represents the metric you're measuring. It's one of the most effective ways to visualize how a metric has performed over a specific period.

You should care about these plots because they help you instantly spot:

  • Trends: Is your monthly revenue generally increasing, decreasing, or staying flat?

  • Seasonality: Do your sales always spike in December and dip in February?

  • Anomalies: Was there a sudden, unexpected drop in website traffic last Tuesday?

  • Impact of Events: How did that new marketing campaign affect user sign-ups day by day after launch?

Without plotting your data over time, you're looking at a static snapshot. A time series plot gives you the complete story, providing the context you need to ask smarter questions.

Step 1: Preparing Your Data

Before you can build anything in Power BI, you need to make sure your data is structured correctly. Garbage in, garbage out. For a time series plot, this is non-negotiable. If your data isn't set up right, your chart won't work.

Key Requirements for Your Dataset

Your data source, whether it's an Excel spreadsheet, a CSV file, or a database, must contain at least two essential columns:

  1. A Time-Based Column: This is a column that contains date or date/time information (e.g., 2023-10-26, 10/26/2023 10:00 AM).

  2. A Numeric Value Column: This is the metric you want to track (e.g., Sales, Revenue, Pageviews, Number of Clicks).

Most importantly, Power BI needs to recognize your time-based column as a date. If it interprets your date column as simple text (a common issue with CSV imports), none of the time-based features will work.

How to Check and Change Data Types

It's always a good practice to verify your data types in the Power Query Editor before building visuals. Here's how:

  1. In Power BI Desktop, click on "Transform Data" from the Home tab. This will open the Power Query Editor.

  2. Find your date column in the data preview.

  3. Look at the small icon next to the column header. A calendar icon means it's recognized as a date. If you see "ABC," it's being treated as text.

  4. To change it, click on the icon (or right-click the column header). A dropdown menu will appear.

  5. Select "Date" or "Date/Time" from the list. Power Query will try to convert the column, and you should see the icon change to a calendar.

  6. Once you're happy with your data types, click "Close & Apply" in the top-left corner to return to the main report view.

Step 2: Creating a Basic Time Series Plot in Power BI

With your data correctly formatted, creating the plot is surprisingly straightforward. The primary visual you'll use for a time series plot is the humble but powerful Line chart.

Building the Chart: Step-by-Step

Follow these steps to get your first time series plot up and running:

  1. Select the Line Chart Visual: In the Visualizations pane on the right-hand side of your report canvas, click on the icon for the "Line chart." A blank chart template will appear on your report.

  2. Add Your Data to the Axes: With the visual selected, look at your Data pane.

    • Find your date column (e.g., 'OrderDate') and drag it into the X-axis field in the Visualizations pane.

    • Find your numeric value column (e.g., 'SalesAmount') and drag it into the Y-axis field.

That's it! Power BI will instantly render a line chart. You might notice that instead of a continuous line showing every single date, it has likely grouped your data by Year. This is because Power BI automatically creates a date hierarchy, which is our next topic.

Step 3: Drilling Down with the Date Hierarchy

One of Power BI's most convenient features is its ability to automatically understand dates. When you drop a date field onto an axis, it doesn't just see the individual days, it sees the hierarchy of Year, Quarter, Month, and Day.

How to Use Drill-Down and Drill-Up

Look at the top right of your newly created chart. You’ll see a few arrow icons. These are your controls for navigating the date hierarchy.

  • The single down arrow (Turn on Drill Down): Click this to activate drill mode. Now, you can click on a data point on your chart (like the point for "2023") to drill down to the next level. If you're viewing Years, clicking "2023" will now show you the Quarters within 2023.

  • The forked down arrow (Expand all down one level): This moves the entire chart to the next level uniformly. If your chart is showing "2022," and "2023," clicking it will change the view to show "2022 Q1," "2022 Q2," etc., for all years in your data.

  • The single up arrow (Drill Up): This moves back to the previous level, for example, from "Month" to "Quarter."

Spend some time clicking these buttons to see how they work. Being able to drill down from a yearly overview to a monthly breakdown — and back to daily values — is incredibly powerful for exploratory analysis.

Switching to a Continuous Axis

Sometimes, you may not want the hierarchical date grouping. You might simply want to see an unbroken line showing how your data trend over time to spot overall tendencies. In that case, you can switch to a continuous axis.

In the Visualizations pane, click the small drop-down arrow next to your date field in the X-axis field well. Instead of selecting "Date Hierarchy," select the plain date field itself. This will render your chart with dates as a continuous sequence, presenting your visual as a more traditional time series plot.

Step 4: Making Your Time Series Plot Clear and Professional

A default chart does the job, but utilizing pre-made visual customization can make your insights stand out more effectively. Click on the blank space in your report to deselect the chart and then select the paintbrush icon ("Format your visual") in the Visualizations pane.

Essential Formatting Options

  • X-Axis & Y-Axis: Adjust the title fonts, size, color, and range. If your data doesn't begin at zero, consider setting the Y-axis minimum value to avoid misleading the insights.

  • Gridlines & Titles: Write a description like "Monthly Sales Trend" instead of the default "Sum of SalesAmount by Year."

  • Data Labels: Add markers or symbols directly on the data points when needed to highlight important events.

Add Good Visual Clarity and Analytics to Your Chart

Power BI goes beyond simple visualization and statistical insights.

Adding a Trend Line

Right from this pane, click the "+" signal next to Forecast or Analytics to activate trend lines.

  1. Select the X-axis, then the Trend Line option.

  2. Click "Add," and a trend line will instantly be stretched across your line chart, helping you observe patterns like a steady increase or a seasonal dip over time.

Using Advanced Analytical Features

This is another part of the same section, allowing you to use parameters or "What-If" scenarios built into the Power BI platform.

Final Thoughts

In conclusion, leveraging time series plots in Power BI allows you to create detailed and informative analytics. By enhancing your chart with features like trend lines and forecast analytics, you further understand your metrics over time. These visual tools provide a comprehensive perspective of your current operations and guide strategic planning and decision-making processes. Keep exploring the capabilities of Power BI and refine your data storytelling techniques to optimize visibility into your business health.