THRESHOLDBYSUM

Walks a running sum over an array and returns the object at which that cumulative sum first reaches or exceeds a threshold.

Syntax

THRESHOLDBYSUM(array, property, threshold)

Arguments

• array: The array of objects to walk, already in the order the running total should accumulate over. THRESHOLDBYSUM does not sort the array itself, use SORTBY first.
• property: The property name to sum as the running total.
• threshold: The value the running sum must reach or exceed.

Example

THRESHOLDBYSUM(
  [{date: 1, amount: 1000}, {date: 2, amount: 2000}, {date: 3, amount: 500}],
  "amount",
  2500
) → Returns {date: 2, amount: 2000}, because the running sum reaches 1000, then 3000 at the second entry, crossing 2500.

A typical use is finding the date a cumulative collection total first crossed a business threshold, without a workflow watching for the live moment it happens. Combine with SORTBY and FLATTEN:

IFERROR(
  THRESHOLDBYSUM(
    SORTBY(FLATTEN(C@CURRREC!N@Invoice!{{Packed Ledgers}}), "date", "asc"),
    "amount",
    {{Total Collected Before Bonus}}
  ).date,
  ""
)

Because it recomputes from the full array every time rather than watching for a live crossing event, this stays correct even if entries are added out of order or backdated after the fact.

Usage Notes:

• Returns the whole matching object, not just the summed value, so you can read any property off it (.date, or any other field on that entry). This matches MAXBY's convention. Wrap with IFERROR and append the property you need, as in the example above.
• Returns undefined if the threshold is never reached, the array is empty, or the input is not an array. Guard with IFERROR.
• A missing or non-numeric property on an element is treated as 0 for that step, it does not break the running sum with NaN.
• A threshold of 0 or less matches on the first element, as long as amounts are non-negative, since the running sum already satisfies the threshold after one step. This is a degenerate case, not a substitute for finding a minimum, for that use MINBY instead.
• THRESHOLDBYSUM is order-dependent. Sorting the array yourself first with SORTBY is required for a meaningful result, it is not done for you.