Forum Discussion
Calculating Forecast Accuracy
I've posted an example of the data table below. In this (simplified) version, there are only 5 rows snapshotted each day (in reality there are thousands). Note that in the example below I've added a dividing line between each snapshot date - in reality, of course, that isn't there.
Hopefully this makes it a little easier to understand.
The idea is that the user is able to select two dates:
- a Snapshot Date (for example, 7/2/18)
- a Close Date (for example, 7/8/18)
I would then sum the Estimated Value for all of the rows in the snapshot taken on Snapshot Date which have an Estimated Close on or before Close Date. That would give me a figure for how much revenue we expect to generate during the period between Snapshot Date and Close Date.
Then I'd sum the Actual Value for all of the rows in the snapshot taken on Close Date which have an Actual Close on or after Snapshot Date. That would give me a figure for how much revenue we actually generated during the period between Snapshot Date and Close Date.
I could then compare those two figures to get an idea of our forecasting accuracy.
I hope all that makes it a little easier to understand what I need.
Snapshot Date Created On Estimated Close Estimated Value Actual Close Actual Value 7/1/2018 6/15/2018 7/23/2018 500.00 7/25/2018 550.00 7/1/2018 6/8/2018 7/11/2018 900.00 7/11/2018 900.00 7/1/2018 5/5/2018 8/24/2018 750.00 7/1/2018 6/19/2018 9/3/2018 1125.00 7/1/2018 6/28/2018 7/15/2018 851.00 7/19/2018 851.00 ------------------------------------------------------------------------------------------------ 7/2/2018 6/15/2018 7/23/2018 500.00 7/25/2018 550.00 7/2/2018 6/8/2018 7/11/2018 900.00 7/11/2018 900.00 7/2/2018 5/5/2018 8/24/2018 750.00 7/2/2018 6/19/2018 9/3/2018 1125.00 7/2/2018 6/28/2018 7/15/2018 851.00 7/19/2018 851.00 ------------------------------------------------------------------------------------------------ 7/3/2018 6/15/2018 7/23/2018 500.00 7/25/2018 550.00 7/3/2018 6/8/2018 7/11/2018 900.00 7/11/2018 900.00 7/3/2018 5/5/2018 8/24/2018 750.00 7/3/2018 6/19/2018 9/3/2018 1125.00 7/3/2018 6/28/2018 7/15/2018 851.00 7/19/2018 851.00 ------------------------------------------------------------------------------------------------ 7/4/2018 6/15/2018 7/23/2018 500.00 7/25/2018 550.00 7/4/2018 6/8/2018 7/11/2018 900.00 7/11/2018 900.00 7/4/2018 5/5/2018 8/24/2018 750.00 7/4/2018 6/19/2018 9/3/2018 1125.00 7/4/2018 6/28/2018 7/15/2018 851.00 7/19/2018 851.00 ------------------------------------------------------------------------------------------------ 7/5/2018 6/15/2018 7/23/2018 500.00 7/25/2018 550.00 7/5/2018 6/8/2018 7/11/2018 900.00 7/11/2018 900.00 7/5/2018 5/5/2018 8/24/2018 750.00 7/5/2018 6/19/2018 9/3/2018 1125.00 7/5/2018 6/28/2018 7/15/2018 851.00 7/19/2018 851.00 ------------------------------------------------------------------------------------------------ 7/6/2018 6/15/2018 7/23/2018 500.00 7/25/2018 550.00 7/6/2018 6/8/2018 7/11/2018 900.00 7/11/2018 900.00 7/6/2018 5/5/2018 8/24/2018 750.00 7/6/2018 6/19/2018 9/3/2018 1125.00 7/6/2018 6/28/2018 7/15/2018 851.00 7/19/2018 851.00 ------------------------------------------------------------------------------------------------ 7/7/2018 6/15/2018 7/23/2018 500.00 7/25/2018 550.00 7/7/2018 6/8/2018 7/11/2018 900.00 7/11/2018 900.00 7/7/2018 5/5/2018 8/24/2018 750.00 7/7/2018 6/19/2018 9/3/2018 1125.00 7/7/2018 6/28/2018 7/15/2018 851.00 7/19/2018 851.00 ------------------------------------------------------------------------------------------------ 7/8/2018 6/15/2018 7/23/2018 500.00 7/25/2018 550.00 7/8/2018 6/8/2018 7/11/2018 900.00 7/11/2018 900.00 7/8/2018 5/5/2018 8/24/2018 750.00 7/8/2018 6/19/2018 9/3/2018 1125.00 7/8/2018 6/28/2018 7/15/2018 851.00 7/19/2018 851.00
Hi Budfudder
Sorry for replying late.
Does the
"a Snapshot Date (for example, 7/2/18)
a Close Date (for example, 7/8/18)"
refer to the "Snapshot Date" and "Actual Close" in the table?
If you add these two columns in the slicers, then select from the slicer, the table visual would only show the data according to the slicer, does this suit your needs?
If so, you could create measures as below
Measure = CALCULATE(SUM(Sheet2[Estimated Value]),FILTER(ALLSELECTED(Sheet2),[Estimated Close]<=MAX([Actual Close]))) Measure 2 = CALCULATE(SUM(Sheet2[Actual Value]),FILTER(ALLSELECTED(Sheet2),[Snapshot Date]<=MAX([Actual Close])))
How would you like to do to "compare those two figures to get an idea of our forecasting accuracy"?
Best Regards
Maggie