How to Add Trend Lines in Power BI charts?

by

Here’s a use case from a reader:

“I have a bunch of monthly sales data which we are trying to analyze using Power BI and need to identify trends over time. I’ve tried adding trend lines but they either don’t show up or don’t reflect the actual pattern. How can I properly add and customize trend lines to my Power BI visualizations to better understand our sales trajectory?”

Adding Built-in Trend Lines in Power BI

Just like creating reference lines in Power BI charts, adding trend lines can provide valuable insights into your data patterns. Let’s explore how to implement both built-in and custom trend lines using practical examples from retail sales data.

Our example uses a Sales table containing: Date (datetime), Revenue (decimal), Units (integer), Product (text), and Region (text). We’ll implement both Power BI’s native trend line feature and create custom trend calculations.

Include Trend line in Power BI line chart:

  1. Create a basic line chart:
  • Drag ‘Date’ to Axis.
  • Then go ahead and drag the ‘Revenue’ column to Values
  1. Enable trend line analysis:
  • Click the Analytics pane (third icon in visualization settings)
  • Expand ‘Trend line’
  • Toggle ‘Show’ to On
  1. Customize trend line appearance:
  • Style: Choose between Linear, Exponential, or Polynomial
  • Color: Select a contrasting color
  • Transparency: Adjust to 25-30% for better visibility
  • Line style: Select Solid or Dashed
  • Weight: Set to 2px for optimal visibility

Note: same procedure applies for column, bar and scatter plots.

Creating Custom Trend Lines Using DAX

  1. Create a time index measure:
TimeIndex = RANKX(ALL(Sales[Date]), Sales[Date],, ASC)
  1. Calculate trend line values:
TrendLine = 
VAR _avgY = AVERAGE(Sales[Revenue])
VAR _avgX = AVERAGE([TimeIndex])
VAR _slope = CALCULATE(
    SUMX(ALL(Sales),
        (Sales[Revenue] - _avgY) * ([TimeIndex] - _avgX)
    ) / SUMX(ALL(Sales),
        ([TimeIndex] - _avgX) * ([TimeIndex] - _avgX)
    )
)
VAR _intercept = _avgY - _slope * _avgX
RETURN _intercept + _slope * [TimeIndex]
  1. Add the trend line to your visual:
  • Create a new line chart
  • Add original Revenue measure
  • Add TrendLine measure as a second line
  • Format lines to distinguish between actual and trend

Chart Trendline now available – what to do?

  • If trend lines don’t appear, check for null values in your data
  • Ensure date field is properly formatted as datetime
  • For seasonal data, consider using polynomial trend lines
  • When dealing with sparse data, use linear interpolation
  • If trend line appears flat, verify calculation range

For more advanced analysis, you can also combine this with dynamic filtering in Power BI to analyze trends across different segments.

Writing trend lines in Power BI – FAQ

Q: Why isn’t my trend line appearing in Power BI?
A: The most common reason is having non-continuous date fields or insufficient data points. Ensure your date column is formatted as a proper Date/Time data type and contains at least 3-4 data points. Also check that your chart type supports trend lines – they work with line, column, bar, and scatter plots, but not with pie charts or tables.

Q: Can I add multiple trend lines to compare different metrics?
A: Yes, you can add trend lines to each measure in your chart independently. In the Analytics pane, each measure will have its own trend line option. This is particularly useful when comparing revenue trends against units sold or comparing performance across different product categories on the same visualization.

Q: How do I interpret polynomial vs linear trend lines?
A: Linear trend lines show constant rate of change and work well for steady growth patterns. Polynomial trend lines (degree 2 or 3) capture curved patterns and are better for seasonal data or when growth rates accelerate or decelerate over time. Use polynomial sparingly as higher degrees can overfit to outliers.