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.