Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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.