Filter measure based on fields from different columns

[kfons]

I am working on a forecast Analysis project and am having a problem with a measure I need to create.
I am able to calculate the total forecast for items by month fairly easily using:

Adj Forecast = CALCULATE(sum(Forecast[Forecast Quantity]))

The problem is that I need to determine what the forecast was at the beginning of the month since during the month there are adjustments made if the forecast is oversold and this Adj Forecast is not a true reflection of what the Product Managers actually forecast.

I need to filter to only sum the forecast for lines with a creation date less than the forecast date and do not exactly know how to write this equation. Something like this (but this doesn't work):

Forecast = CALCULATE(sum(Forecast[Forecast Quantity]),FILTER(Forecast[creation date]<forecast[forecast date]))

To make it even more complex I may need to filter where the Creation Date is less than the forecast date +7 days


Any help on this equation would be awesome.


Forecast Table Data example (forecast and Adj forecast are the measures I need):

[TABLE="width: 480"]
<tbody>[TR]
[TD]Item No.[/TD]
[TD]Forecast Date[/TD]
[TD]Creation Date[/TD]
[TD]Forecast QTY[/TD]
[TD]Forecast[/TD]
[TD]Adj Forecast[/TD]
[/TR]
[TR]
[TD]801[/TD]
[TD="align: right"]4/1/2013[/TD]
[TD="align: right"]4/26/2012[/TD]
[TD="align: right"]6,500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]801[/TD]
[TD="align: right"]4/1/2013[/TD]
[TD="align: right"]11/29/2012[/TD]
[TD="align: right"]-3,500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]801[/TD]
[TD="align: right"]4/1/2013[/TD]
[TD="align: right"]4/25/2013[/TD]
[TD="align: right"]1,000[/TD]
[TD="align: right"]3,000[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD]801[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]6/5/2012[/TD]
[TD="align: right"]4,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]801[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]11/29/2012[/TD]
[TD="align: right"]-1,000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]3000[/TD]
[/TR]
</tbody>[/TABLE]

</forecast[forecast>

 

[kfons]

I figured it out

CALCULATE(sum(Forecast[Forecast Quantity]),FILTER(Forecast,Forecast[Creation Date]<=Forecast[Creation Date]))

Is there a better formula that would result in improved performance? Perhaps using the Measure Adj Forecast and somehow then filtering it?