Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Show Last 12 Months Data in Power BI

Cody Schneider••8 min read

Showing a rolling 12-month view of your data is one of the most common and powerful ways to analyze business trends. Instead of getting locked into calendar years, a rolling view gives you immediate, up-to-date context on performance. Ditching that manual Monday morning export and getting this view live in Power BI is a total game-changer. This tutorial will walk you through three different methods for displaying the last 12 months of data in Power BI, from a simple filter setting to more powerful and flexible DAX formulas.

GraphedGraphed

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.

Watch Graphed demo video

Why a "Rolling 12-Month" View is So Important

Before jumping into the "how," it's worth quickly touching on the "why." A static year-over-year report can sometimes be misleading. A massive sales spike last December might make this January look weak in comparison, even if you’re trending upwards month-over-month. A rolling 12-month analysis smooths out this calendar-based weirdness.

It helps you answer more relevant questions, such as:

  • Is our performance trending up or down, based on a full year of context?
  • How does this month's performance compare to the trailing average?
  • Are we still feeling the effects of a campaign we ran nine months ago?

This dynamic viewpoint keeps your analysis relevant and focused on continuous trends, not just arbitrary calendar boundaries.

Method 1: The Quick Fix with Relative Date Filtering

This is the easiest, no-code way to filter a page or visual to the last 12 months. It's built right into Power BI’s filter pane and is perfect for quick reports or for users who aren't ready to dive into DAX just yet.

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.

Step-by-Step Guide

  1. Select your visual or click on the report canvas: First, decide the scope. Do you want to filter one specific chart or the entire report page? If you want to filter a single chart, click on it. To filter the whole page, just click on the blank report canvas.
  2. Open the Filters pane: This is typically located on the right-hand side of the Power BI interface.
  3. Drag your date field into the filter well: Find your primary date column (e.g., 'Order Date', 'Transaction Date') from your data table in the Fields pane. Drag and drop it into the "Filters on this page" or "Filters on this visual" box.
  4. Change the filter type to "Relative date": Click on the dropdown menu under "Filter type" and select "Relative date".

You will now see a simple interface to configure the filter:

  • In the "Show items when the value" field, keep the selection as "is in the last."
  • In the text box, type 12.
  • In the dropdown next to it, select months.

And that’s it! The visual or page will now automatically display only the data from the past year, starting from today's date. The "Include today" checkbox allows you to decide whether to include partial data from the current day.

When to use this method: When you need a fast and simple filter for a standalone report and don't need to reuse this logic in complex calculations.

Method 2: Create a Reusable Filter with a DAX Calculated Column

The filter pane method is great, but it has limits. It doesn't allow you to easily compare the "Last 12 Months" period to prior periods. For that, you need a more robust solution. By creating a calculated column using DAX (Data Analysis Expressions), you can build a permanent, reusable flag in your data model that categorizes each date as being within the last 12 months or not.

The Case for a Dedicated Date Table

Before you write any time-based DAX, you absolutely need a proper Date Table (sometimes called a Calendar Table). This is a foundational best practice in Power BI. A good Date Table is a continuous list of dates covering the full timespan of your data, with no gaps.

If you don't have one, you can create one easily with DAX. Go to the "Data" view, click "New Table" from the "Table tools" ribbon, and use a formula like this:

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

Replace the start and end dates with a range that covers all of your business data. Once created, make sure to mark it as a date table by right-clicking the table in the Fields pane and selecting "Mark as date table." Finally, create a relationship between the date column in your new Date Table and the date column in your main data table (e.g., 'Sales'[OrderDate]).

GraphedGraphed

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.

Watch Graphed demo video

Building the "Is Last 12 Months?" Flag

With your Date Table in place, you can now add the calculated column.

  1. Navigate to the Data view (the little spreadsheet icon on the left).
  2. Select your Date Table from the Fields pane on the right.
  3. From the "Column tools" ribbon at the top, click "New column."
  4. Enter the following formula into the formula bar:
Is in Last 12 Months = 
VAR TodayDate = TODAY()
VAR DateDiffInMonths = DATEDIFF('Date Table'[Date], TodayDate, MONTH)
RETURN
IF(
    DateDiffInMonths >= 0 && DateDiffInMonths < 12,
    "Last 12 Months",
    "Older Data"
)

Breaking Down the Formula:

  • TodayDate = TODAY(): this stores the current date in a variable. It ensures the calculation is always up-to-date every time the report is refreshed.
  • DateDiffInMonths = DATEDIFF('Date Table'[Date], TodayDate, MONTH): this is the core of the logic. DATEDIFF calculates the number of boundaries (in this case, months) between each date in your Date Table and today's date.
  • IF(DateDiffInMonths >= 0 && DateDiffInMonths < 12, "Last 12 Months", "Older Data"): this logic checks two things. >= 0 filters out future dates, and < 12 ensures that only dates within the last 12-month period are included. If a date meets this criterion, it's labeled "Last 12 Months," otherwise, it's labeled "Older Data."

Using Your New Column

Now that the column exists in your model, you can use it just like any other field. Go back to the "Report" view, and drag the "Is in Last 12 Months" field into the Filters pane. Uncheck "Older Data," and you have a clean, reusable filter that everyone using your dataset can leverage.

When to use this method: When you need a consistent slicer or filter that everyone can reuse across visuals and pages, and want to easily compare the "Last 12 Months" bucket to other periods.

Method 3: Dynamic Calculation with a DAX Measure

Measures are the most powerful and flexible way to perform calculations in Power BI. Unlike a calculated column, a measure isn't stored in your model and doesn't take up memory - it’s calculated on-the-fly when you use it in a visual. This is perfect for calculating a specific value (like revenue, signups, or traffic) over a rolling 12-month period.

Writing the Rolling 12-Month Sales Measure

Let's say you already have a basic measure for total sales, like Total Sales = SUM(Sales[Revenue]). Now, you want to create a sister measure that calculates sales only for the last 12 months.

  1. Click anywhere on your report canvas, then click "New measure" from the "Home" ribbon.
  2. Enter the following formula:
Sales Last 12 Months = 
CALCULATE(
    [Total Sales],
    DATESINPERIOD(
        'Date Table'[Date],
        LASTDATE('Date Table'[Date]),
        -12,
        MONTH
    )
)

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.

Breaking Down the Formula:

  • CALCULATE([Total Sales], ...): CALCULATE is arguably the most important function in DAX. It changes the context in which a calculation is performed. Here, we're taking our existing [Total Sales] measure and applying a new date filter to it.
  • DATESINPERIOD(...): This is an amazing time-intelligence function that returns a table of dates.

Together, this DAX measure tells Power BI: "Run the Total Sales calculation, but only include dates within the 12-month period ending on the last visible date."

Visualizing Your New Measure

You can now use this measure to see your rolling 12-month trend. For instance, you could create a line chart where the X-axis is your 'Date Table'[Month], and you have two values in your Y-axis: Total Sales and Sales Last 12 Months. You will immediately see a smoother trend line for your 'Sales Last 12 Months' measure, giving you a much clearer picture of your business's momentum.

When to use this method: When you need to calculate an aggregated value (sales, users, costs) over a flexible, rolling period. It's the best option for performing trend analysis within your visualizations.

Final Thoughts

Getting a handle on rolling 12-month calculations transforms your reports from static snapshots into dynamic analytical tools. We've covered three ways to accomplish this in Power BI, from the simple drag-and-drop relative date filter to reusable DAX calculated columns and highly flexible DAX measures. Choose the approach that best fits your specific need and comfort level.

Learning DAX and a tool like Power BI is a powerful skill, but it requires a lot of time and effort to master. If you've ever spent a frustrating afternoon trying to figure out DATESINPERIOD or wondering why your filters aren't working, you're not alone. We exist to eliminate that friction. With Graphed you simply connect your data sources, then tell us in plain English what you want to see - like “Show me my revenue by product for the last 12 months.” We instantly generate the dashboard, no DAX required, with live data that's always up to date.

Related Articles