How to Show Rolling 12 Months in Power BI

Calculating a rolling 12-month trend is a powerful way to see your business performance without the noise of monthly fluctuations. Instead of laboring over complex formulas or manually exporting data, you can build this directly in Power BI. This tutorial will walk you through creating a dynamic rolling 12-month calculation using DAX, step by step.

What Exactly is a Rolling 12-Month Calculation?

A rolling 12-month (R12M), also known as a trailing twelve-month (TTM) calculation, is a financial metric that shows a company's performance over its most recent 12-month period. It’s a dynamic calculation that's not tied to the calendar year. Instead of looking at January to December, it sums up the data from, for example, last June to this May.

Why is this so useful?

• It smooths out seasonality: By always including a full 12-month cycle, you get a more stable and accurate picture of your growth, minimizing the impact of seasonal peaks and valleys.
• It provides timely insights: You can spot trends as they emerge, rather than waiting until the end of the year to analyze performance. It tells you what's happening right now.
• It offers consistent comparison: Every data point on the trendline represents a full year of activity, making comparisons from month to month more meaningful. An R12M value for April can be directly compared to May, telling you how your most recent 12-month period stacks up against the one just prior.

Prerequisite: The Non-Negotiable Date Table

Before you even think about writing time intelligence formulas, you need a proper date table. This is the single most important component for working with dates in a Power BI model. Why? Because DAX time intelligence functions like the ones we'll use require an unbroken, contiguous list of dates to work their magic.

Your sales table or Google Analytics data may have gaps in dates (e.g., no sales on a holiday). A dedicated date table solves this by providing a complete calendar for your model.

How to Create a Date Table with DAX

If you don't already have one, creating a date table is simple. Go to the "Data" view in Power BI, select the "Table tools" tab, and click "New table." Enter this DAX formula:

Date Table = 
CALENDARAUTO()

The CALENDARAUTO() function will automatically scan your model for the earliest and latest dates and create a continuous calendar that spans that entire period. You can then add more columns for Month, Year, and Quarter for better reporting.

A more custom approach uses the CALENDAR() function:

Date Table = 
ADDCOLUMNS(
  CALENDAR(DATE(2021, 1, 1), DATE(2024, 12, 31)),
  "Year", YEAR([Date]),
  "Month Name", FORMAT([Date], "mmmm"),
  "Month Number", MONTH([Date]),
  "Quarter", "Q" & FORMAT([Date], "q")
)

Mark as Date Table and Create Relationships

Once your table is created:

1. Make sure you have your new 'Date Table' selected.
2. Go to the "Table tools" tab and click "Mark as date table".
3. In the dialog box, select the main date column from your table (often called "[Date]").
4. Finally, go to the "Model" view and drag a relationship from the date column in your Date Table to the date column in your fact table (e.g., your 'Sales Data' or 'Web Traffic' table). The relationship should be one-to-many, flowing from the Date Table to your fact table.

With that foundation in place, you're ready to build the calculation.

Step-by-Step: Creating a Rolling 12-Month Measure in DAX

Let's say we have a simple sales table with an 'Order Date' and sales 'Amount'. We'll create a measure that calculates the sum of sales for the preceding 12 months from any given month in our visuals.

Step 1: Create a Base Measure for Your Total

First, we need a simple measure that calculates the metric you want to track - in this case, total sales. This isolates the initial calculation and makes your more complex formulas easier to read and debug.

In the "Report" view, click "New measure" and enter:

Total Sales = SUM('Sales Data'[Amount])

Step 2: Write the Rolling 12-Month DAX Formula

Now for the main event. Create another new measure. We will use the powerful DATESINPERIOD function, which is perfect for this task.

Rolling 12 Sales = 
CALCULATE(
  [Total Sales],
  DATESINPERIOD(
    'Date Table'[Date],
    MAX('Date Table'[Date]),
    -12,
    MONTH
  )
)

Step 3: Breaking Down the Formula

This formula may look complicated, but each part has a specific job:

• CALCULATE([Total Sales], ...): The core of most DAX measures. It tells Power BI to calculate our [Total Sales] measure, but with a modified filter context. The second part of the formula defines how we want to change that context.
• DATESINPERIOD(...): This is an iterator that returns a table of dates.

Putting it all together, the measure tells Power BI: "For any given month, calculate the [Total Sales] over a period that starts from the last day of that month and extends back 12 months."

Visualizing Your Rolling 12-Month Trend

Now that you have your [Rolling 12 Sales] measure, using it is easy. The best visual for showing trends is often a line chart:

1. Drag a line chart from the Visualizations pane onto your report canvas.
2. Drag your date field (e.g., Month or Date) from your Date Table onto the X-axis.
3. Drag your new [Rolling 12 Sales] measure onto the Y-axis.
4. (Optional) Add your base [Total Sales] measure too. You'll see a volatile monthly sales line alongside a much smoother, clearer trendline from your rolling calculation.

It helps contextualize the numbers and highlights how a rolling calculation provides a more stable view of performance.

Common Issues and Quick Fixes

Sometimes your DAX doesn't behave as expected. Here are a couple of common stumbling blocks and how to solve them.

Problem: The "Total" in my table or matrix visual looks incorrect.

Reason: Your rolling measure works perfectly for each row (like a month or quarter), but the grand total calculation uses the MAX(Date) for the entire context. This means the total simply shows you the R12M value for the very last date in your dataset, not a sum of the months above.

Solution: You can wrap your formula in a quick check to provide a more meaningful total, or just return it blank if a single period isn't selected.

Rolling 12 Sales (Fix for Total) =
IF(
    ISFILTERED('Date Table'[Year]) || ISFILTERED('Date Table'[Month Name]),
    CALCULATE(
        [Total Sales],
        DATESINPERIOD(
            'Date Table'[Date],
            MAX('Date Table'[Date]),
            -12,
            MONTH
        )
    )
)

This version checks if the measure is being calculated in the context of a filtered year or month. If it is (on a row), it does the calculation. If not (in the total row), it returns blank.

Problem: My rolling calculation returns blank values.

Reason: This almost always comes back to the date table setup.

• The relationship between your Date Table and fact table may be missing, inactive, or incorrect.
• You may have accidentally used the date column from your fact table in your visual's axis instead of the one from your Date Table.
• The Date Table might not be marked as a date table in the model.

Solution: Go through the "Prerequisite" steps again. Confirm your model is set up with a dedicated Date Table, it's marked as such, and it has a single, one-to-many relationship with your main data table.

Final Thoughts

Mastering the rolling 12-month calculation in Power BI transforms your reports from static snapshots into dynamic performance dashboards. The process is straightforward if you remember the core tenets: build a reliable date table, create a base measure, and then use DAX time intelligence functions like DATESINPERIOD to define your desired timeframe.

While DAX is incredibly powerful, we know that learning its syntax and troubleshooting formulas can sometimes feel like a full-time job. At Graphed, we've automated this entire process. Instead of writing and debugging DAX, we allow you to simply describe what you need using natural language. For example, queries like "show me our monthly revenue for the last 18 months, and add a rolling 12-month average" can instantly create the dashboards, removing manual data wrangling while you focus on analysis. If you'd like an easier way to connect your data and analyze real-time performance, you can get started with Graphed for free.