Calculated field: Sum on every row/date if date on row is between start_date and end_date on those rows in dataset

I’m looking to do something similar to below code in SQL:

(SELECT SUM(sales) FROM dataset WHERE record_start <= AND record_end >= AS all_sales
FROM dataset dt

So basically, sales are constant for each record, for each dat between record_start and record_end. What the calculated field is supposed to output is a sum of the amount for all records that have record_start <= row date and record_end >= row date. Hope screenshot below explains it a bit better. Thanks

Hi @variable, You can use level aware calculations (LAC) to achieve what you are look for. Refer to below blog to see some examples of LAC. Hope this helps.


Thank you, @Karthik_Tharmarajan .
However, I’m having trouble figuring this one out. How would LAC work when the condition for aggregation is for date to be between start_date and end_date of those rows in the dataset.
Any help here would be greatly appreciated.

Any help here would be greatly appreciated.

As I understand it, level aware calculations (LAC) would sum record_sales and group by date. That’s not quite what I need, but rather sum record sales for all records that on that day are active (record_start <= date AND record_end >= date).

This would be pretty easy at the database level, but this is not my dataset, I’m just receiving it and can’t change it at source.