What is Rolling 12 Months in Power BI?

Calculating a rolling 12-month total is one of the most powerful ways to understand business performance, and doing it in Power BI turns a static number into a dynamic, updating metric. This article will walk you through exactly what a rolling 12-month calculation is, why it matters, and how to create one step-by-step using DAX.

What Is a Rolling 12-Month Calculation?

A rolling 12-month calculation, often called Trailing Twelve Months (TTM) or Last Twelve Months (LTM), is a measurement of a company's performance using data from the past 12 consecutive months. Instead of looking at a fixed calendar year (January 1st to December 31st), it looks at the 12-month period ending on the last day of the most recent month.

For example, if it’s currently March 2024, your rolling 12-month revenue would be the total revenue from April 2023 through March 2024. Next month, in April 2024, the calculation will automatically "roll" forward to cover May 2023 through April 2024.

This is an incredibly valuable metric for a few key reasons:

• It Smooths Out Seasonality: Most businesses have natural peaks and valleys. A toy company’s sales spike in December, while a landscaping company does better in the summer. A rolling 12-month view includes a full seasonal cycle, giving you a more stable and accurate picture of your year-over-year growth trend without being skewed by a single good or bad month.
• It Provides Up-to-Date Perspective: Waiting until the end of the year to analyze performance is too slow. TTM provides a consistent, updated view of how you're doing based on a full year's worth of recent data, allowing for quicker and better-informed decisions.
• It Creates Better Comparisons: Comparing this March to last March can be misleading if there was a one-off event. Comparing the TTM ending this March to the TTM ending last March is a much more robust, apples-to-apples comparison of your business's health.

Getting Your Data Model Ready in Power BI

Before you can write any DAX formulas, you need a solid foundation. For any time-based calculations like a rolling 12 months, the most important piece of that foundation is a dedicated Date Table.

A Date Table (or Calendar Table) is a separate table in your Power BI model that contains a continuous list of dates, along with useful columns like year, month, quarter, and day of the week. It acts as the single source of truth for all time-related analysis.

Why a Date Table is Non-Negotiable

You might be tempted to just use the date column from your sales data, but this can cause problems. DAX time intelligence functions are designed to work with a complete, unbroken list of dates. Your sales data likely has gaps (like weekends or holidays where no sales occur). Using a dedicated Date Table solves this and makes your formulas reliable.

Creating a Basic Date Table with DAX

If you don't already have a Date Table, creating one in Power BI is straightforward. Navigate to the Data view, select the Table tools ribbon, and click New table. You can then use a DAX function to generate the dates for you.

A simple way to do this is with the CALENDARAUTO() function, which automatically scans your entire data model for the earliest and latest dates and creates a continuous calendar between them.

Date = CALENDARAUTO()

Once you've created this table, you can add more columns for month, year, quarter, etc., to make slicing and dicing your data easier. For example, to add a year column, click New column and use this formula:

Year = YEAR('Date'[Date])

You can do the same for the month:

Month Name = FORMAT('Date'[Date], "mmmm")

Mark as Date Table and Create Relationships

After creating your table, you need to tell Power BI that this is a special table to be used for time intelligence.

1. Select your new Date Table.
2. In the Table tools ribbon, click Mark as date table.
3. In the dialog box, select the column that contains the unique dates (in our case, the 'Date' column).

Finally, go to the Model view and create a relationship. Drag the date column from your Date Table (e.g., 'Date'[Date]) and drop it onto the corresponding date column in your fact table (e.g., 'Sales'[OrderDate]). This should create a one-to-many relationship, with the 'one' side on the Date Table and the 'many' side on your Sales table.

With this setup, you are ready to build your rolling 12-month measure.

Calculating Rolling 12 Months with DAX

Now for the main event: writing the DAX formula. We'll build this up in two simple steps: creating a base measure for our sales, then creating the rolling 12-month calculation on top of it.

Step 1: Create a Base Measure

It's always a best practice to create an explicit base measure for any value you plan to analyze. Let's create a measure for total sales. Find your Sales table in the Fields pane, right-click, and select New measure. Then, enter this simple formula:

Total Sales = SUM(Sales[SalesAmount])

Using a base measure like this makes your other formulas cleaner and easier to manage. If you ever need to change how "Total Sales" is calculated, you only have to update it in one place.

Step 2: Create the Rolling 12-Month Measure

Now, we'll create the measure that calculates the TTM sales. Right-click your Sales table again, select New measure, and enter the following DAX formula:

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

Let's break down exactly what this formula is doing:

• CALCULATE([Total Sales], ...): This is the superstar of DAX functions. It modifies the filter context for a calculation. Here, we're telling it to calculate our [Total Sales] measure, but not for the default time period (like a single month). Instead, we're giving it a new, custom time period to use.
• DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -12, MONTH): This function generates the custom time period we need. It returns a table containing a column of dates. It needs four pieces of information:

Putting it all together, the formula calculates the [Total Sales] over a dynamic period that starts from the last visible date in your report and goes back exactly 12 months from there.

Visualizing Your Rolling 12-Month Performance

The beauty of this measure comes to life when you visualize it. Comparing your standard monthly sales to your rolling 12-month sales on the same chart clearly demonstrates the smoothing effect and reveals the underlying trend.

Here’s how to build a quick and effective visual:

1. Select a Line and clustered column chart from the Visualizations pane.
2. Drag your base measure, [Total Sales], to the Column y-axis.
3. Drag your new measure, [Rolling 12 Months Sales], to the Line y-axis.
4. Drag a date field, like the Month and Year from your Date Table, to the Shared x-axis.

What you'll see is powerful. The columns will show the volatile up-and-down nature of your regular monthly sales. The line, however, will be much smoother. If that line is trending upwards, your business is growing, even if you’ve had a few slow months. If it's trending downwards, it's an early warning sign that performance is slipping, giving you time to act before it's too late.

Final Thoughts

Creating a rolling 12-month calculation in Power BI distills complex sales data into a clear, actionable trend line. By setting up a proper Date Table and using a simple but powerful DAX formula with CALCULATE and DATESINPERIOD, you can create a dynamic report that automatically updates and provides a much more accurate view of your business's health over time.

For many teams, learning DAX and data modeling can feel like a major hurdle in getting these kinds of clear insights. That's why we built Graphed. It uses AI to handle the complexity for you. Instead of writing formulas, you can simply connect your data sources and ask questions in plain English like, "show me a line chart of my trailing twelve-month revenue compared to last year," and get a live, interactive dashboard built for you in seconds.