AGGREGATE

Returns the aggregated result of a measure over the specified time period.

The AGGREGATE formula performs the specified aggregation, such as minimum/maximum/average/sum, on a measure over a specified time range.

Syntax

AGGREGATE(measure, startDate, endDate, aggregationType)

Arguments

measure – The measure to be aggregated. Required.

startDate – Start date of the period range. Required.

endDate- End date of the period range. Required.

aggregationType - Aggregation method like AVG/SUM/MIN/MAX/MEDIAN. Required.

Return value

Returns the custom aggregation applied on the measure over the specified date range.

Example

AGGREGATE([Profit], SHIFT(CURRENT_PERIOD, "-1M"), SHIFT(CURRENT_PERIOD, "-3M"), "MIN")
//Calculates the minimum of the profit in the past 3 months

AGGREGATE([Sales], DATE(2024,4,1), DATE(2024,8,31), "MEDIAN")
//Calculates the median of sales between April 2024 and August 2024

The AGGREGATE function has been used to find the minimum sales three months prior to each month in the report.

AGGREGATE