Calculating Forecast Accuracy

[Budfudder]

I have a table containing potential deals, titled Deals. It contains, for each potential deal, an estimated value, an estimated close date, an actual value (completed only when the deal is closed) and an actual close date (completed only when the deal is closed). We have snapshots of this table taken daily going back some time.

I want to show two figures:

- the user selects/inputs a date (call this Snapshot Date). I add the estimated value of all deals not yet closed from the snapshot taken on the Snapshot Date.

- the user selects/inputs another date (call this Closed Date). I add the actual value of all deals which have been closed since the Snapshot Date (using the actual close date column) from the entries created on the Created Date.

So I have to let the user select two different dates and then use these to make calculations, adding values from the table which satisfy certain conditions (table's snapshot date = Snapshot Date value, etc.).

How can I best let the user select the two different dates? I tried using a slicer around the Snapshot Date, and treating the latest date selected as the Closed Date, but I couldn't even get the figures from the slicer. Is there a better way to do this? Or am I barking up entirely the wrong tree?

[v-juanli-msft]

Hi @Budfudder

It is not clear for me about the dataset and requirements,

could you show some data for example?

Best Regards

Maggie

[Budfudder]

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

[v-juanli-msft]

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