Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Get Max Date in Power BI

Cody Schneider••9 min read

Finding the very last date in your dataset is one of the most common and useful tasks in Power BI. Whether you need to see the date of the most recent sale, calculate month-to-date performance, or simply add a "Data as of..." note to your report, getting the maximum date is fundamental. In this tutorial, we’ll walk through the essential DAX functions for this job - MAX, MAXX, and LASTDATE - with practical, step-by-step examples for each.

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 Finding the Maximum Date is So Important

Before jumping into DAX formulas, let's look at a few common scenarios where you'll need to find the latest date. This isn't just an abstract exercise, it's a practical skill that unlocks deeper insights and makes your reports more dynamic and user-friendly.

  • Showing "Last Activity" KPIs: Displaying the "Last Sale Date" or "Last Customer Interaction" on a card visual gives your audience immediate context about the freshness of the data.
  • Calculating Period-to-Date Metrics: Accurate Year-to-Date (YTD) or Month-to-Date (MTD) calculations depend on identifying the latest date within the current period.
  • Filtering for the Latest Data: You might want a report page that always defaults to showing data just for the most recent day or week in your dataset.
  • Creating Dynamic Titles: Adding a title like “Sales Report Through October 26, 2024” that updates automatically makes your report more professional and trustworthy.

Thankfully, DAX (Data Analysis Expressions), Power BI's formula language, gives us several ways to handle these tasks. Let's meet the main players.

Your Main Tools: MAX(), MAXX(), and LASTDATE()

At first glance, these three functions might seem to do the same thing. While they are all related to finding the "last" or "biggest" date, they interact with your data model and report filters in critically different ways. Understanding their unique behaviors is the key to using them correctly.

  • MAX(): The simplest of the bunch. It scans a single column and returns the single largest value (or latest date).
  • MAXX(): An "iterator" function. It goes through a table row by row, performs a calculation or check for each row, and then returns the maximum value from the results.
  • LASTDATE(): A time intelligence function. It returns the last date within the current filter context, making it perfect for dynamic calculations on visuals.

Let's break each one down with a real-world example using a sample 'Sales' table that includes columns like [Order Date], [Product], [Revenue], and [Region].

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.

Method 1: Using the Simple MAX() Function

The MAX() function is your most straightforward tool. It looks at all the values in a specified column and returns the highest one. When used with a date column, it returns the most recent date in that entire column, ignoring most report filters you might apply.

This makes it ideal for when you need a single, unchanging value that represents the absolute last date in your entire dataset.

Practical Example: Creating a "Last Refreshed Date" Card

Let's create a measure to always show the last date a sale was recorded in our database. We can then display this measure in a card visual so users always know how current the report's data is.

Step-by-Step Instructions:

  1. In Power BI Desktop, navigate to the Report view.
  2. In the Fields pane on the right, right-click on your 'Sales' table.
  3. Select New measure. This will open the formula bar at the top.
  4. Enter the following DAX formula:
Last Sale Date = MAX('Sales'[Order Date])
  1. Press Enter to save the measure. You will now see "Last Sale Date" in your 'Sales' table in the Fields pane.
  2. Now, select the Card visual from the Visualizations pane and drag it onto your report canvas.
  3. Drag your new Last Sale Date measure into the "Fields" area of the card visual.

And that's it! The card will now show the latest date from your 'Sales'[Order Date] column. No matter how you filter your report (e.g., by region or product), this date will remain the same, as MAX() looks at the entire column by default.

Method 2: Getting Granular with the MAXX() Function

MAXX() is a more powerful and flexible version of MAX(). As an iterator function, it doesn't just look at a column, it evaluates an expression for every single row in a table you specify. This allows you to find the max date based on specific conditions.

Its syntax is: MAXX(<table>, <expression>)

The key here is that you can use a filtered version of your table as the first argument. This is where MAXX() really shines.

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

Practical Example: Find the Last Sale Date for a Specific Region

Imagine your boss wants to know the date of the very last sale specifically in the "West" region. MAX() can't do this easily, as it looks at the whole column. But MAXX() is perfect for it.

Step-by-Step Instructions:

  1. Right-click on your 'Sales' table again and select New measure.
  2. This time, we will use the FILTER() function inside MAXX() to create a virtual table that only contains sales from the "West" region. Enter this formula:
Last Sale Date (West) = 
MAXX(
    FILTER(
        'Sales', 
        'Sales'[Region] = "West"
    ), 
    'Sales'[Order Date]
)

Let's break down what's happening:

  • FILTER('Sales', 'Sales'[Region] = "West"): This first part creates a temporary table that includes only the rows where the [Region] column is "West".
  • MAXX(..., 'Sales'[Order Date]): MAXX then iterates over this temporary table (row by row) and finds the maximum value in the [Order Date] column for just those filtered rows.

Now, if you add this new measure to a card visual, it will show you the date of the last sale in the West region, regardless of any other filters on the page. This is incredibly useful for comparing regional performance or KPIs that require filtered date context.

Method 3: Working with Filters Using LASTDATE()

The LASTDATE() function is a specialized tool from the time intelligence family. Unlike MAX() and MAXX(), its primary purpose is to respect the filters currently active in your report (like slicers or visual filters).

It returns a single-column table containing the last date in the current context. This makes it the go-to function for dynamic measures like "Sales on the Last Visible Day."

Heads-up: Time intelligence functions like LASTDATE() work most reliably when you have a dedicated Date Table in your data model, which is a best practice for any serious Power BI report. If you don't have one, LASTDATE() can still work on a date column, but a date table is highly recommended.

Practical Example: Calculating Sales on the Most Recent Day

Let's build a measure that calculates the total sales for the last day displayed in a table or on a chart. If the user filters the report for "August 2024," this measure should return the sales total for the last day in August that had sales.

First, make sure you have a basic sales measure, like:

Total Revenue = SUM('Sales'[Revenue])

Now, let's create our new measure using LASTDATE().

Step-by-Step Instructions:

  1. Create a New measure in your 'Sales' table.
  2. Enter the following DAX formula. We'll use CALCULATE() to modify the context of our existing Total Revenue measure:
Revenue on Last Day = 
CALCULATE(
    [Total Revenue],
    LASTDATE('Sales'[Order Date])
)

Here's the logic:

  • CALCULATE([Total Revenue], ...): Tells Power BI to calculate the [Total Revenue] measure, but with a special filter applied.
  • LASTDATE('Sales'[Order Date]): This function finds the last date based on what is currently visible or selected, and that single date becomes the filter that CALCULATE uses.

If you add this measure to a table visual alongside months, you'll see it correctly calculate the revenue only for the last date of each respective 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.

Which Function Should You Use? A Quick Guide

Feeling a little confused about which one to pick? No worries. Here’s a simple cheat sheet:

  • Use MAX() when... You need the absolute, overall latest date from your entire dataset for something like a "data as of" card. It's simple, fast, and ignores filters.
  • Use MAXX() when... You need to find the latest date based on a specific, complex condition applied row-by-row (e.g., the last order date for products with profit over $100).
  • Use LASTDATE() when... You are creating a dynamic measure that needs to respond to user filters on the report page, especially for time intelligence calculations (like daily performance within a selected month).

Bonus Tip: Create a Dynamic Report Title

Let's put this into practice with one final, polished touch. A report title that automatically updates itself is always impressive.

  1. Create a New measure.
  2. Use this formula to combine static text with your max date. The FORMAT function lets you control how the date is displayed.
Dynamic Report Title = 
"Sales Performance through " & FORMAT(MAX('Sales'[Order Date]), "mmmm d, yyyy")
  1. Add a Card visual to your report.
  2. Drag the Dynamic Report Title measure into the Card visual's field well.
  3. Finally, in the Card's formatting options, turn off "Category label" and adjust the font size to make it look like a title.

Now your report title will always be current, reflecting the latest data you've loaded.

Final Thoughts

Learning how to get the maximum date in Power BI unlocks a new level of interactivity and professionalism in your reports. By understanding the different behaviors of MAX, MAXX, and LASTDATE, you can confidently choose the right tool for whatever your analysis requires, from simple high-level KPIs to complex, context-aware calculations.

These DAX functions are powerful, but we know first-hand that there is a significant learning curve. We built Graphed because we wanted to eliminate that friction. Instead of searching for the right formula, you can simply connect your data sources and ask questions in plain English like, "show me our total sales on the most recent day" or "create a dashboard of our sales KPIs." Graphed instantly handles the underlying complexity and builds live, interactive dashboards for you in seconds.

Related Articles