Power BI DAX MAX Function [With Examples]

When you build a sales dashboard in Power BI Desktop for a retail company, one of the most common requests from a sales manager is: “Show me the latest order date and the highest sales amount by month on one screen.” They want a quick way to see the top numbers without scrolling through tables or exporting data back to Excel.

In these situations, the Power BI DAX MAX function becomes one of your most useful tools. You use it to find the latest transaction date, the highest invoice value, the largest discount, or the maximum quantity sold in a period — and then surface those values as KPIs, cards, or filters inside the report.

In this article, you’ll learn exactly how the DAX MAX function works in Power BI, step-by-step, with practical examples you can apply directly to your own sales dashboard.

What Is the Power BI DAX MAX Function?

The DAX MAX function in Power BI returns the largest numeric value or the latest date from a column or a list of expressions. In plain language, MAX answers questions like “What is the highest?” or “What is the latest?”.

At a very basic level, you can think of MAX as doing the same job as the MAX function in Excel, but working inside a data model with filters, relationships, and row context.

Basic MAX Syntax

The core syntax looks like this:

MAX(<column>)

• <column> is a single column from a table in your data model, such as Sales[Amount] or Sales[OrderDate].
• It returns the largest value from that column after applying any filters on the report.

There is also an overload that accepts two expressions:

MAX(<expression1>, <expression2>)

This version compares two values you provide manually or through measures and returns the larger of the two, similar to MAX(A1, B1) in Excel.

When and Why You Use MAX in Power BI

In real projects, MAX shows up in a lot of common scenarios. Here are a few you’ll see again and again:

• Showing the latest transaction date in a card visual.
• Displaying the highest sales amount for the selected month.
• Highlighting the maximum discount or maximum quantity in a matrix.
• Creating dynamic alerts for KPIs when a value exceeds a threshold.
• Building measures that compare two metrics and pick the higher one.

On a sales dashboard, for example, you might have a Date table, a Sales table, and visuals like Clustered column charts, Cards, and Slicers. MAX helps you convert raw transactional data into clear answers like “What’s the highest sales value in the current month?” or “What’s the latest order date?”

If you are just getting started with DAX and measures, you may find it useful to also review a few related concepts, such as Power BI DAX basics from your main DAX overview page and how to filter and clean data using Power Query (for example, removing blanks or converting text to dates) before using MAX in measures.

Example Data Model Setup (Retail Sales Dashboard)

To make the examples concrete, let’s stick to a simple sales dashboard scenario:

• Sales table
  Columns: OrderID, OrderDate, Customer, Product, Quantity, Amount, Discount.
• Date table
  Columns: Date, Year, Month, MonthName, Quarter.

You create relationships:

• Date[Date] is related to Sales[OrderDate] (single-direction, one-to-many).
• You mark the Date table as the official Date table to support time intelligence.

Typical visuals:

• A Clustered column chart showing Total Sales by MonthName.
• A Card visual showing Max Sales Amount.
• A Slicer for Year and MonthName to filter the report.

With this setup, MAX becomes a natural part of your DAX measures.

Example 1 – MAX on a Numeric Column in Power BI (Highest Sales Amount)

The simplest use of MAX is finding the highest sales amount from the Sales table. Suppose you already created a base measure:

Total Sales = SUM(Sales[Amount])

This measure adds up all Amount values in the Sales table for the current filter context.

Now, to get the highest single transaction amount (one row in the Sales table), you can write:

Max Sales Amount = MAX(Sales[Amount])

How It Works in a Card Visual

1. Create the Max Sales Amount measure.
2. Add a Card visual to your report page.
3. Put Max Sales Amount in the card’s Fields.
4. Add a Slicer for Year and MonthName from the Date table.
5. Select a year and month.

The card now shows the highest single Sales[Amount] value from all transactions that match the selected year and month.

This is very useful when your sales manager wants to see “What was our biggest order this month?” without digging through tables.

Pro Tip: I’ve found that using MAX on transaction amounts works best when your data model doesn’t mix currencies or units in the same column. If you store different currencies in one Amount column, convert everything to a common currency before applying MAX, otherwise the “highest” number can be misleading.

Example 2 – MAX on a Date Column in Power BI (Latest Order Date)

Another very common use case is finding the latest order date. This is especially helpful in monitoring data refresh or checking whether recent orders are loaded properly into the model.

Create this measure:

Latest Order Date = MAX(Sales[OrderDate])

Here is what’s happening:

• MAX scans the Sales[OrderDate] column.
• It returns the largest (latest) date after filters.
• If you filter by Year or a specific customer, it only considers the visible rows.

Displaying Latest Order Date in a Power BI Card Visual

1. Add a Card visual.
2. Drag the Latest Order Date measure to the card.
3. Optionally, format the measure using the date format in the Modeling tab.
4. Add slicers for Year and Customer.

Now, when your sales manager chooses a specific customer and year, the card shows the last date that customer placed an order.

This is also handy when you check whether your data refresh in Power BI Service is bringing in yesterday’s data. If the latest date looks stale, you know something is wrong with your refresh or source.

Example 3 – Comparing Two Measures Using MAX in Power BI

Sometimes you need to pick the larger of two measures. For example, you might have:

Online Sales = SUM(Sales[OnlineAmount])
Store Sales  = SUM(Sales[StoreAmount])

You want a single measure that returns whichever is higher between Online Sales and Store Sales for the current filters.

You can write:

Max Channel Sales = MAX([Online Sales], [Store Sales])

Then you can:

• Use Max Channel Sales in a Card visual.
• Add a Legend to your chart showing online vs store sales.
• Build a KPI that alerts you when Max Channel Sales crosses a target.

This is a simple but powerful way to compare metrics without building a complex IF logic.

Example 4 – MAX with CALCULATE and Filter Conditions in Power BI

In more advanced scenarios, you might want to find the maximum value under specific conditions, such as the highest sales amount for a product category or region.

Let’s say you have a ProductCategory column in the Sales table. You want the highest sales amount for “Electronics” in the current year.

Max Electronics Sales =
CALCULATE(
    MAX(Sales[Amount]),
    Sales[ProductCategory] = "Electronics"
)

What’s happening here:

• CALCULATE changes the filter context to include only rows where ProductCategory is “Electronics”.
• Then MAX returns the highest Sales[Amount] within that filtered set.
• Any existing filters like Year still apply.

This pattern is especially useful when you build KPI cards or conditional formatting in tables based on the maximum values for particular segments.

If you’re comfortable with this pattern, you’ll also find it easier to explore related topics like filtering in DAX and conditional formatting based on field value in Power BI for more advanced reporting logic.

Example 5 – MAX Date for Period-Based Measures in Power BI

The MAX function also plays nicely with date-based logic, especially when you track the latest available date in your Date table or Sales table.

Suppose you want to always show the latest available month in a visual, even when new data arrives. You can start by finding the latest date in your Date table:

Latest Date = MAX('Date'[Date])

Then, use this measure in other calculations, like filtering your visuals to only show the period up to this date. This makes your report dynamic and keeps your interactive report focused on the most relevant data.

For example, you might:

• Use Latest Date in a measure that filters your visuals to the last N days.
• Combine Latest Date with time intelligence functions for current month reports or rolling periods.

If you work a lot with date filters, you may also want to learn how to filter last N days using DAX and how date slicers behave when you anchor them to measures.

Things to Keep in Mind (Best Practices and Common Mistakes)

• Use MAX for single columns, not whole tables: MAX works on one column or two expressions, not on entire tables. If you try to pass a table, it will throw an error. Always reference a specific column like Sales[Amount].
• Understand filter context: MAX respects current filters from slicers, visuals, and Row-Level Security (RLS). If your MAX result looks wrong, check which filters are active on the report.
• Avoid using MAX on text columns: MAX is designed for numeric and date columns. If you run it on text, you’ll get comparison based on sorting rules, which is rarely what you want for business logic.
• Use measures instead of calculated columns for aggregations: When you need the highest value by filter (like “highest amount this month”), use a measure with MAX, not a calculated column. Calculated columns don’t react to slicers and page filters.
• Combine MAX with CALCULATE for segmented logic: If you need the max value for specific categories or segments, wrap MAX inside CALCULATE with the right filter conditions. This is more flexible than hard-coding filters in visuals.
• Check data quality first: Before trusting MAX for key KPIs, clean your data (remove invalid dates, nulls, or incorrect amounts) using Power Query. Garbage in means misleading maximum values out.

Frequently Asked Questions

You’ve seen how the Power BI DAX MAX function helps you find the highest values and latest dates, build clearer KPIs, and support real business questions on your dashboard. The best approach is to always think about filter context, data quality, and whether you should combine MAX with CALCULATE or other DAX functions to match your actual reporting needs. I hope you found this article helpful.

You May Also Like

• Power BI DAX basics and common functions
• Power BI Date Slicer Only Shows Dates With Data
• DAX FILTER for advanced filtering
• Remove filters in Power BI DAX for flexible measures
• Power BI DAX MAX Date for latest period reporting
• Difference between calculated column and measure in Power BI