I'm trying to figure out how to create a calculated column based on an over function. I can do this using AllPrevious([DATE]), but how do I do this before a specific date (1/4/2023)?

[Anthony Giambalvo]

This formula works for me and gets all previous data...Sum([AVG_GAS_RATE_MCFD]) OVER (Intersect([WELL_NME],AllPrevious([DATE])))

How do I replace AllPrevious([DATE]) with a specific date? I'm only concerned with data prior to 1 April 2023 and I need to do this with a calculated column.

[Gaia Paolini]

you could set up your calculated column as (use whatever date format you have for the specific date)

case when DateDiff('day',Date('01/04/2023'),[DATE])<=0 thenSum([AVG_GAS_RATE_MCFD]) OVER (Intersect([WELL_NME],AllPrevious([DATE])))end

but this effectively means truncating the value at the specified date and having null values after that. Is this what you had in mind?

[Anthony Giambalvo]

Gaia,

Thank you so much.

That's exactly what I was after.

Because I'm generating a forecast from a specific date in time, I don't want to show any of the data after that date.

The above worked like a charm, thank you!