Forecast Vs. Actual Revenue Report

Hello all,

I have a TRANSACTIONS table with the fields Forecast Date, Actual Date, and Transaction Amount. I would like to create a single monthly forecast vs. revenue summary report based on this table. The challenge here is that summary reports only allow us to select a single date field for the grouping. It can either be based on the Forecast Date or the Actual Date, but not both. So if I use the Forecast Date (month/year) as my group header, the "Actual Revenue" column will only summarize records that have a forecast date during that month/year, rather than all records that have an "Actual Date" of that month/year regardless of when they were forecasted for.

Of course, the opposite is also true. If I use the "Actual Date" as the report driver, then the "Forecast Revenue" column will only summarize records that have a "Forecast Date" in that Actual Date month, rather than summarizing everything that was forecasted for that month, regardless of what month they became actual revenue.

I hope this makes sense.

Any ideas on how this can be accomplished?

Thanks in advance!

------------------------------
Admin Entry
------------------------------