How to Plot Last 12 Months in Power BI

Getting your Power BI report to dynamically show data for the last 12 months should be simple, but it can quickly become frustrating. Manually adjusting date filters every month is tedious, and static slicers can't keep up with your latest data. This guide will walk you through setting up a truly dynamic, rolling 12-month view in your dashboard. You'll learn two reliable methods using DAX, ensuring your reports are always showing the most relevant and up-to-date trend information.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why is a Rolling 12-Month View So Important?

Before jumping into the formulas, it's helpful to understand why a "rolling" or "trailing" 12-month view is a standard in business reporting. It's about context and consistency.

• Consistent Comparisons: A static "year-to-date" view compares months against an ever-changing baseline. A rolling 12-month view always analyzes a full year's worth of performance, making it easier to spot seasonal trends and compare recent performance to the same period last year without needing complex visuals.
• Always Current: When a new month begins, a rolling view automatically drops the oldest month and adds the newest one. This means your dashboard is always current, showing the most recent full-year trend without you lifting a finger. Your audience is always looking at right-now analysis, not an outdated snapshot.
• Smoothed-Out Trendlines: Looking at data week-over-week or even month-over-month can be noisy and reactive. A trailing 12-month period smooths out short-term fluctuations, revealing the bigger picture and the true direction your key metrics are headed.

Ultimately, it moves your report from being a simple data dump to a strategic tool for analysis that remains relevant over time.

The Essential First Step: Create a Date Table

Every robust Power BI model needs a dedicated date table (sometimes called a calendar table). This is non-negotiable for any kind of time intelligence calculation. Without one, the DAX functions we're about to use won't work correctly. A date table is a separate table in your model that contains a continuous list of dates, along with useful columns like year, month name, quarter, and week number.

Here's why it's so critical:

• It provides a single, reliable source for all date-based filtering.
• It guarantees there are no missing dates, which can break time calculations.
• It's a requirement for many powerful time intelligence DAX functions, like the ones we'll use.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

How to Create a Basic Date Table with DAX

If you don't already have one, creating a date table is straightforward. Navigate to the Data view in Power BI, select "New Table" from the Home tab, and paste in a DAX formula.

The CALENDARAUTO() function is often the easiest, as it automatically scans your model for the earliest and latest dates and builds a calendar to cover that range.

Date Table = CALENDARAUTO()

For more control, you can use the CALENDAR() function, where you specify a start and end date.

Date Table = CALENDAR(DATE(2022, 1, 1), DATE(2025, 12, 31))

Once you have your table, add a few calculated columns to make filtering easier. Select your new date table, click "New Column," and add formulas like these:

• Year: Year = YEAR([Date])
• Month Name: Month Name = FORMAT([Date], "mmmm")
• Month Number: Month Number = MONTH([Date])
• Year-Month: Year-Month = FORMAT([Date], "YYYY-MM")

The Most Important Step

After creating your date table, you must:

1. Mark it as a date table: Right-click the table in the Fields pane, go to "Mark as date table," and select the main date column.
2. Create a relationship: Go to the Model view and drag the date column from your new Date Table to the corresponding date column in your main data table (e.g., your Sales table's OrderDate or your Web Analytics table's EventDate). This connects your calendar to your events.

With a proper date table in place, you're ready to build your rolling 12-month logic.

Method 1: The Calculated Column 'Flag' Approach

This method is intuitive and great for beginners. The idea is to add a "flag" column to your date table that simply tells you if a given date falls within the last 12 months. You can then use this simple column to filter any visual on your report.

Step 1: Add a New Column to Your Date Table

Navigate to your Date Table, select "New Column," and paste the following DAX formula:

IsInLast12Months = VAR TodaysDate = TODAY() VAR TwelveMonthsAgo = EDATE(TodaysDate, -12) RETURN IF( 'Date Table'[Date] <= TodaysDate && 'Date Table'[Date] > TwelveMonthsAgo, "Last 12 Months", "Older" )

How This Formula Works

• VAR TodaysDate = TODAY(): This creates a variable that stores today's current date.
• VAR TwelveMonthsAgo = EDATE(TodaysDate, -12): This creates a second variable by taking today's date and going back exactly 12 months using the EDATE function.
• RETURN IF(...): The IF statement checks each date in your date table. If a date is less than or equal to today's date and is more recent than 12 months ago, it labels it as "Last 12 Months." Otherwise, it labels it as "Older."

Step 2: Use the Flag as a Filter

Now, you can simply use this new IsInLast12Months column as a filter.

• Click on the visual you want to filter (or on the report canvas itself for a page-level filter). Drag the IsInLast12Months column from your Date Table into the "Filters on this page" or "Filters on this visual" pane.
• Select the "Last 12 Months" option. Done!

Pros and Cons of the Flag Method

• Pros: Very easy to understand and implement. Any user of the report can see how the filter works.
• Cons: A calculated column is only refreshed when the dataset is refreshed. This means the TODAY() function only updates when you refresh the data, so it might be a day out of date depending on your refresh schedule. Slicing with this is a toggle, not a slider, which may be limiting for certain analyses.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 2: Using a DAX Measure for Dynamic Filtering (The Pro Method)

A more flexible and performant approach is to use a DAX measure. Unlike a calculated column, measures are calculated on-the-fly based on the context of your report (e.g., slicers, filters). This method creates a metric that specifically calculates a value (like total sales) only for the last 12 months from the latest date in your selected context.

Step 1: Create a Base Measure

First, ensure you have a simple base measure for the metric you want to track. If you don't already, create one. For example:

Total Revenue = SUM(Sales[Revenue])

Working with measures is a Power BI best practice, rather than directly using columns in your visuals.

Step 2: Create Your Rolling 12-Month Measure

Now, create a new measure that builds on your base measure.

Revenue L12M = CALCULATE( [Total Revenue], DATESINPERIOD( 'Date Table'[Date], LASTDATE('Date Table'[Date]), -12, MONTH ) )

How This Formula Works

• CALCULATE([Total Revenue], ...): CALCULATE is the powerhouse function of DAX. It modifies the context in which another calculation is performed. Here, we're telling it to calculate our [Total Revenue] measure, but with a special date filter applied.
• DATESINPERIOD(...): This is the core time intelligence function. It returns a table of dates.

Essentially, this measure calculates total revenue, but only for the dates returned by the DATESINPERIOD function - which is a rolling 12-month period.

Step 3: Use the Measure in Your Visuals

Now, instead of using your original Total Revenue field in a chart, use your new Revenue L12M measure. When you plot this over a date axis (e.g., from your Date Table), the line or bar chart will automatically display the rolling 12-month calculation for each month.

Pros and Cons of the Measure Method

• Pros: Extremely dynamic and responsive. It recalculates based on any filters or slicers applied, making it ideal for exploratory analysis. Better performance on very large datasets as it's not stored in memory like a calculated column.
• Cons: The concept can be a little less straightforward for absolute beginners. You're creating a new calculation rather than a simple filter category.

Common Pitfalls and Best Practices

While building your reports, keep these points in mind to avoid common frustrations:

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Always Use a Proper Date Table

I can't state this enough. Ninety percent of time intelligence problems in Power BI stem from a missing or improperly configured date table. Make sure yours is marked as a date table and has relationships to your fact tables.

Check Your Relationships

A single, one-to-many relationship should run from your date table (the "one" side) to your data table (the "many" side). If these are inactive or going in the wrong direction, your DAX will fail.

Understand Context

A quick note on the measure method: using LASTDATE() as the anchor makes it flexible. This way, if you have a month slicer set to June 2023, the formula will calculate the 12 months leading up to June 30, 2023. This is incredibly powerful for historical analysis.

Final Thoughts

Whether you choose the simple calculated column "flag" or the more powerful DAX measure, both methods will give you a dynamic rolling 12-month view in your Power BI reports. Getting this right is a fundamental step in moving from static reporting to building an analytics tool that provides continuous, relevant insights for your business.

Manually writing DAX and setting up these dynamic filters is a core part of building BI reports, but it often involves a steep learning curve and constant upkeep to get it just right. At Graphed , we decided to automate these tedious parts of data analysis. Instead of creating measures, you can just ask in plain English, "show me a breakdown of our revenue by month for the last 12 months," and get a live-updating dashboard in seconds. We connect directly to your data sources, handle all the modeling, and let you create entire reports using chat, so you can focus on the insights in your data, not the formulas needed to find them.