How to Show Latest Month Data in Power BI

One of the most common requirements for any business dashboard is to automatically show results from the most recent month. Whether you're tracking sales KPIs, marketing performance, or operational metrics, you need your report to stay current without manually changing the filters every time the data refreshes. In this tutorial, you’ll learn three effective and easy-to-follow methods to set up Power BI to always display the latest month's data.

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

The Essential Foundation: A Dedicated Date Table

Before we dive into the specific methods, we need to cover the most important best practice in any Power BI analysis involving time: using a dedicated date table. While you might be tempted to use the date column directly from your sales or marketing data, a separate date table gives you far more power and flexibility.

A date table is a simple table with one column containing a continuous sequence of dates, from your earliest transaction to your latest. You then add other useful columns like Year, Month, Quarter, and Day of Week. This table acts as your single source of truth for all things time-related.

How to Create a Date Table with DAX

Creating a date table is simple. In Power BI Desktop, go to the Data view, select the Table tools tab, and click New table. Then, enter the following DAX formula:

Dates = 
ADDCOLUMNS (
    CALENDARAUTO(),
    "Year", YEAR ( [Date] ),
    "MonthNumber", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "mmmm" ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "YearMonth", FORMAT ( [Date], "YYYY-MM" )
)

This formula does two things:

• CALENDARAUTO(): Scans your entire data model for dates and automatically creates a column of all dates from the earliest to the latest year it finds.
• ADDCOLUMNS(): Adds helpful columns for slicing and dicing your data, like the year, month number, and month name.

After creating the table, go to the Model view, find your new 'Dates' table, and create a relationship by dragging its '[Date]' column to the corresponding date column in your main data table (e.g., 'Sales'[OrderDate]). Finally, right-click the 'Dates' table and select Mark as date table to let Power BI know how to use it for time intelligence.

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

Method 1: The DAX Calculated Column (The Most Flexible Approach)

This method works by adding a "flag" to your date table that identifies which dates belong to the latest month found in your data. It's the most robust solution because it's based on the actual data you have, not just today's calendar date. This is perfect for analyzing historical data or situations where data refreshes might lag by a few days.

Step 1: Identify the Latest Date in Your Fact Table

First, we need to find the most recent date in your primary data table (like your sales or traffic data). Let's assume your main table is called 'Sales' and its date column is 'OrderDate'. The goal is to compare every date in our 'Dates' table to this single most recent date.

Step 2: Create the Calculated Column

With your 'Dates' table created, we'll add a new column to it. In the Data view, select your 'Dates' table, then click New column from the toolbar. Enter this DAX formula:

Is Latest Month = 
VAR MaxTransactionDate = MAX('Sales'[OrderDate])
VAR MaxTransactionMonth = MONTH(MaxTransactionDate)
VAR MaxTransactionYear = YEAR(MaxTransactionDate)
VAR CurrentRowMonth = 'Dates'[MonthNumber]
VAR CurrentRowYear = 'Dates'[Year]
RETURN
IF(
    AND(CurrentRowYear = MaxTransactionYear, CurrentRowMonth = MaxTransactionMonth),
    "Latest Month",
    "Prior Months"
)

Let's break down this formula:

• VAR MaxTransactionDate = MAX('Sales'[OrderDate]): Finds the latest date in your entire 'Sales' table.
• Stores the month and year of that latest date in separate variables.
• Retrieves the month and year for the current row being evaluated in the 'Dates' table.
• RETURN IF(...): Checks if the year and month of the current row match those of the latest transaction date. If yes, labels "Latest Month", otherwise, "Prior Months."

Step 3: Apply the Filter to Your Report

Now for the easy part. Go back to your Report view. With no visuals selected, find your Is Latest Month column in the Fields pane and drag it to the Filters on this page section in the Filters pane.

In the filter card that appears, select "Latest Month". That’s it! All visuals on your report page will now be filtered to show only data from the most recent month in your dataset. When your data is refreshed next month, the calculated column updates automatically, and your report switches seamlessly to the new latest month.

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: The Relative Date Filter (The Easiest Quick Win)

If you don't want to write any DAX, Power BI has a built-in filter that can help. The "Relative Date" filter is incredibly intuitive and quick to set up. Its primary limitation, however, is that it is based on today's date, not the latest date within your dataset.

This method is ideal for datasets that are refreshed daily and are always up-to-date.

Step-by-Step Guide:

1. Click on the report canvas to ensure no visuals are selected. This allows you to set a page-level filter.
2. In the Fields pane, find the Date column from your dedicated 'Dates' table and drag it into the Filters on this page bucket in the Filters pane.
3. In the new filter card, click the dropdown under "Filter type" and select Relative date.
4. Configure the options that appear:
5. Click Apply filter.

Your report page is now filtered to the current calendar month. You could also set it to "is in the last 1 calendar month" if you prefer that dynamic behavior. This approach is lightning-fast, but remember an important caveat: if we are in July but your dataset was last refreshed with June's data, a report filtered to "this calendar month" will be blank.

Method 3: Filtering with Power Query (For Performance on Huge Datasets)

Our third method happens before dashboards or DAX ever come into play. By using the Power Query Editor, you can filter your data before it's even loaded into the Power BI model. This can significantly improve report performance and reduce file size, which is critical when working with massive datasets measured in millions of rows.

The trade-off is that this method removes all other data from your model. You won't be able to easily create visualizations comparing this month to previous months, as that data will have already been filtered out.

Step 1: Open the Power Query Editor and Find the Max Date

From the Power BI Desktop home ribbon, click Transform data. In the Power Query Editor, find your main data query (e.g., 'Sales') in the left pane. First, isolate the latest date.

Right-click the 'Sales' query and select Reference. This creates a new query pointing to your original data. Rename this new query LatestDate.

Select the date column (e.g., 'OrderDate') in the LatestDate query, then go to the Transform tab and click Statistics > Maximum. This transforms the table into a single value: the latest date.

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

Step 2: Add a Conditional Column in Your Main Query

Go back to your main 'Sales' query. In the Add Column tab, click Custom Column. Name the new column "Filter" and enter the following M formula:

if Date.IsInPreviousNMonths([OrderDate], 0) then "Keep" else "Remove"

Hold on! The formula above has the same TODAY() limitation. Let's write one that uses the LatestDate value we created. Here's a more robust version:

if Date.Year([OrderDate]) = Date.Year(LatestDate) and Date.Month([OrderDate]) = Date.Month(LatestDate) then "Keep" else "Remove"

This checks whether the year and month of the order date match those of the latest date in your dataset.

Step 3: Filter and Load

After creating the column, you'll see "Keep" and "Remove" values. Click the filter arrow on the 'Filter' column and uncheck "Remove". This discards all rows not in the latest month. Right-click the header of the 'Filter' column and select Remove to clean your table. Then click Close & Apply. Your data model now contains only data for the latest month.

Which Method Is Right for You?

With three options, which should you choose? Here's a quick guide:

• Use Method 1 (DAX Calculated Column): This is your default. It's the most flexible, works with lagged data, and allows historical comparisons.
• Use Method 2 (Relative Date Filter): Choose this for very simple, recent data views that are always current up to today. Fast and easy.
• Use Method 3 (Power Query Filter): Best when dealing with very large datasets where performance is critical. Less flexible for historical analysis.

Final Thoughts

Knowing how to dynamically filter your reports to the latest month is a fundamental skill that moves your reporting from static to truly automated. Whether you use the robust flexibility of a DAX column, the simplicity of the relative date filter, or the performance gains of a Power Query step, you can now build reports that are always current and relevant.

While mastering these techniques in Power BI is valuable, sometimes you need answers faster, without wading through DAX formulas or filter panes. With Graphed, we’ve simplified this entire process. You connect your data and then just ask what you want to see: “Show my sales from last month by product category.” We handle fetching and calculating the right data in real-time to give you an instant visualization, letting you focus on the insights instead of the setup.