If you've ever used the DISTRIBUTE function, which requires two date inputs, you may be familiar with its syntax:

DISTRIBUTE (start_date, end_date, amount) FROM DATASET

Unlike DISTRIBUTE, which requires both a start and end date, DATE_SUM allows you to aggregate values with just one date parameter. This simplicity makes it especially useful for cases where a single date, such as a closing date or transaction date, determines in which time a certain amount is allocated. Additionally, DATE_SUM includes an optional "last" parameter to help you work with snapshot datasets, avoiding duplicate data entries.

This function can be used both in actuals and versions formulas.

Syntax explanation

DATE_SUM(date, measure, [criteria]) FROM DATASET

• measure: The numeric field (e.g., amount or value) you want to sum.
• date: The date field on which the allocation is based.
• criteria (optional): If "last" is added, it restricts the aggregation to only the last available data load, which is useful for snapshot datasets to avoid duplicates.

Examples:

DATE_SUM (close_date, amount) FROM DATASET

• Sums a certain amount based on close_date date, using all available data in the dataset.
• Use Case: Ideal for calculating total sales or expenses over time on a transactional dataset .

DATE_SUM (close_date, amount, last) FROM DATASET

• Sums a certain amount based on close_date date, but only uses the most recent data load.
• Use Case: Ideal for snapshot datasets (e.g., monthly or quarterly reports) where only the latest entries should be considered, avoiding potential duplicate entries from previous data loads.

Learn more about this function in this link.