How to create a time series chart with trend lines in Power BI

João Barros 20 de August de 2026 4 min read

This tutorial shows how to create a time series chart in Power BI with trend lines and a simple forecast. It is useful to analyze the evolution of sales, visits, or metrics over time and identify patterns and deviations.

Prerequisites

Power BI Desktop installed.
A table with Date and Value columns (e.g.: Date, Sales).
Basic knowledge of modeling (relationships) and DAX.

Step 1: Prepare the date table

A date table (Calendar) is essential for time series: it allows grouping by month, quarter and using time intelligence functions correctly.

// Exemplo DAX para criar uma Calendar table Calendar = ADDCOLUMNS (   CALENDAR (DATE(2020,1,1), DATE(2024,12,31)),   "Year", YEAR([Date]),   "MonthNumber", MONTH([Date]),   "MonthName", FORMAT([Date], "MMMM"),   "YearMonth", FORMAT([Date], "YYYY-MM") )

After creating the table, mark it as "Date table" in Modeling and ensure that the Date column is the date column.

Step 2: Relate the Calendar to the data

Create a relationship between Calendar[Date] and your fact table (e.g.: Sales[Date]). Use a 1:* relationship with the Calendar on the 1 side.

Step 3: Create basic measures

Create measures to total the value and to compare with previous periods. Measures are essential for trend lines and simple forecasts.

// Medida total Total Sales = SUM(Sales[Amount])  // Medida - vendas no período anterior Sales Prev Period = CALCULATE([Total Sales], DATEADD(Calendar[Date], -1, YEAR))

Step 4: Create the line chart

Add a Line chart visual. Put Calendar[Date] on the X axis and the Total Sales measure in Values. Configure the axis to show continuous date (Type: Continuous) if you want smooth lines.

Step 5: Add a trend line with a DAX measure

To have control and make the trend line explicit, calculate a linear trend via simple regression (slope and intercept in DAX) and draw it as an additional series.

// Exemplo de tendência linear (mínimo viável) Trend_Slope = VAR X = AVERAGEX(VALUES(Calendar[Date]), VALUE(FORMAT(Calendar[Date], "YYYYMM"))) VAR Y = AVERAGEX(VALUES(Calendar[Date]), [Total Sales]) VAR XY = AVERAGEX(VALUES(Calendar[Date]), VALUE(FORMAT(Calendar[Date], "YYYYMM")) * [Total Sales]) VAR XX = AVERAGEX(VALUES(Calendar[Date]), VALUE(FORMAT(Calendar[Date], "YYYYMM")) * VALUE(FORMAT(Calendar[Date], "YYYYMM"))) VAR N = COUNTROWS(VALUES(Calendar[Date])) RETURN DIVIDE(XY - N * X * Y, XX - N * X * X)  Trend_Intercept = VAR X = AVERAGEX(VALUES(Calendar[Date]), VALUE(FORMAT(Calendar[Date], "YYYYMM"))) VAR Y = AVERAGEX(VALUES(Calendar[Date]), [Total Sales]) VAR Slope = [Trend_Slope] RETURN Y - Slope * X  Trend Line = VAR CurrX = VALUE(FORMAT(MAX(Calendar[Date]), "YYYYMM")) RETURN [Trend_Intercept] + [Trend_Slope] * CurrX

Note: this example uses YYYYMM as a numeric representation of time for simple regression. For different intervals adjust the X transformation.

Step 6: Show the trend line on the chart

Add the Trend Line measure to the same Line chart as an additional series. You will see the line that represents the linear trend over time. If you prefer, switch the axis to Type: Continuous for a more natural appearance.

Step 7: Create a simple forecast

You can extend the trend to forecast future values. Create an auxiliary table with future dates and use the same Trend Line formula to calculate estimates.

// Tabela de datas futuras (exemplo 6 meses) FutureCalendar = CALENDAR (MAX(Calendar[Date]) + 1, EDATE(MAX(Calendar[Date]), 6))  // Medida para forecast em contexto de FutureCalendar Forecast Value = VAR CurrX = VALUE(FORMAT(MAX(FutureCalendar[Date]), "YYYYMM")) RETURN [Trend_Intercept] + [Trend_Slope] * CurrX

Combine the regular Calendar and the FutureCalendar in a combined visualization or use a Line chart that accepts both series (you can join the tables with UNION if needed).

Verify the result

Confirm that the chart shows the original series and the trend line. Check for dates with null values, outlier points and whether the trend line follows the expected direction. Common errors: not marking the date table, using a discrete axis instead of continuous, and aggregations at the wrong level (using Date vs YearMonth).

Conclusion

You now have a time series chart with a trend line and a simple forecast in Power BI. Next steps: experiment with smoothing (moving average), use R or Python for advanced forecasting, or optimize the regression for different frequencies. Tip: test results with a holdout (set aside some months to validate the forecast).