MAX() function

Return the highest value from a list of values across time periods


🗒️ Description

The MAX() function is used to return the highest value from a set of values. It works across constants, indicators, or properties and returns the maximum value for each time period individually.

This is helpful when applying caps, choosing the best-case output, or applying safeguards like “whichever value is higher” across inputs.


For example, if you’re comparing different sales region revenues and want to track only the top-performing one each month, this function helps summarize that without manual checking or writing IF logic.

✏️ Syntax & Variables

1 max(value1, value2, ..., valueN)

value1, value2, …, valueN: A list of values to compare. Each value can be:

  • A constant number (e.g., 100)
  • An indicator name (e.g., Total Revenue)
  • A dimension property (e.g., Product Category)

The function returns the maximum value across these inputs for each corresponding time period.

✅ Expected result & examples

The MAX() function is incredibly useful in business planning and financial modeling, primarily for setting floors, selecting the most optimistic or best-case scenario, or ensuring minimum performance levels.

1. Ensuring Values Don't Go Below Zero (Non-Negative Values):

  • Scenario: Many financial metrics (e.g., Cash Balance, Inventory Levels, Gross Profit) cannot realistically be negative. You want to ensure that your model's calculations don't produce negative results in these cases, even if underlying inputs might temporarily suggest them.

  • Formula: MAX('Calculated Cash Balance', 0)

  • Result: This formula guarantees that the Calculated Cash Balance will never display a value less than zero. If the calculation otherwise yields -500, MAX will return 0. This is crucial for maintaining the integrity and realism of financial statements and operational metrics.

2. Setting a Floor (Ensuring a Minimum Value):

  • Scenario: You want to ensure that a calculated value, such as a product price or headcount, never falls below a certain threshold to maintain profitability or adequate staffing.
  • Formula: MAX('Calculated Price', 'Minimum Acceptable Price')
  • Result: This ensures that your price or headcount for any given period will always be at least the Minimum Acceptable Price, providing a safety net for profitability or staffing levels.

3. Best-Case Revenue or Performance Forecasting:

  • Scenario: When forecasting sales, you might have different sales teams providing optimistic and conservative estimates. You want to model based on the most optimistic projection for strategic planning or goal setting.
  • Formula: MAX('Sales Team A Forecast', 'Sales Team B Forecast', 'Management Optimistic Estimate')
  • Result: This ensures your revenue forecast reflects the highest potential, which can be useful for aspirational targets or understanding upside potential.

4. Ensuring Minimum Inventory Levels (Safety Stock):

  • Scenario: You need to maintain a specific level of safety stock, and your calculated inventory might sometimes drop below it due to demand fluctuations.
  • Formula: MAX('Calculated Inventory Level', 'Safety Stock Threshold')
  • Result: This ensures that your modeled inventory level never falls below the specified Safety Stock Threshold, preventing stockouts in the model.

5. Selecting the Top-Performing Entity:

  • Scenario: A company operates across multiple regions, and for internal reporting, they want to highlight the revenue generated by the highest-performing region each month.
  • Formula: MAX('Region 1 Revenue', 'Region 2 Revenue', 'Region 3 Revenue', 'Region 4 Revenue')
  • Result: This provides a quick way to identify and track the maximum revenue achieved by any single region for each period.

6. Bonus or Commission Calculation (Choosing the Highest Earning Path):

  • Scenario: An employee's bonus or commission can be calculated based on different criteria (e.g., individual sales, team sales, profit margin achieved), and they receive the higher of the eligible amounts.
  • Formula: MAX('Individual Sales Bonus', 'Team Sales Commission', 'Profit Margin Incentive')
  • Result: This formula automatically determines the highest possible payout based on the various performance metrics, ensuring the employee receives the maximum eligible incentive.

These examples demonstrate how MAX() provides flexibility and control in financial models, allowing users to define minimums, select optimal outcomes, and highlight top performance directly within their calculations.