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
------------------------------
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
------------------------------
