Timing Issue

[clondono]

Hello,

I need to calculate the actuals against the most recent forecast.  In the data I have actuals for the current month but since the month is in progress, I need to exclude current month values from my chart.  The logic should be:

Show actuals when the most current forecast (using FORECAST_DATE) no longer includes forecast data for that month.

I'm using the following expression but I can't get the correct results:

=if((Max(Date(FORECAST_DATE, 'YYYY-MM')))< (Date(Today(), 'YYYY-MM') AND (FORECAST_AMT = 0), (sum(ACTUAL_AMOUNT)),0)

Thanks for your help!

[sunny_talwar]

May be this:

If(MonthStart(Max({<FORECAST_AMT = {0}>} FORECAST_DATE)) < MonthStart(Today()), Sum(ACTUAL_AMOUNT), 0)

I am not sure if the above is going to work because I am not sure what you dimension is and how your data is structured. If it doesn't work, I would suggest providing a sample

[clondono]

Thank you Sunny, let me give this a try and I will let you know if it works, otherwise I will upload a sample file.