2 Replies Latest reply on Mar 5, 2012 11:44 AM by Tania.Skinner

# How to forecast future performance based on past actuals using multiple measures

Hi - I have a very tricky problem that I suspect involves custom table calculations but is too complex for my limited capabilities.  The need overall is to look at past performance, compare to future goals, and determine if the future goals will be met based on the most recent actual data.   A description of the data:

Milestone:  Point in time where a performance measurement is taken.  Each milestone has specific goals that must be measured and tracked.

Goal:  A specific item that is being measured.  Has a target value and an actual.

Target:  The value that each goal is measured against.

Actual:  The actual for that measurement.

Operand:  The type of numeric comparison : <=, >=, =

Met:  Whether the goal is met or not met

Attached spreadsheet has example of data.  Example:

At milestone A1, the goal was to have <= 10 items open.  The actual was 9 so the goal was met.  What I want to know is based on the actuals at A1, can we forecast whether or not the milestone A2 goal will be met.  Where this gets tricky is that A2 has a different target than A1.  A2's target for the same goal is <= 8 items open.  So I want to compare the A1 actual of 9 to the A2 goal of 8 so my forecast would tell me that I was currently NOT meeting that goal based on A1 performance. As you will see in the spreadsheet, each milestone has multiple goals where each milestone's targets are different.   I don't have a specific output format I need - at this point I am just looking for anyway to have Tableau create a report that summarizes for a milestone whether or not the goals are trending to be met based on the current milestone's target and the previous milestone's actuals.

Can anyone help me figure out how to do this?

• ###### 1. Re: How to forecast future performance based on past actuals using multiple measures

Hi Tania,

You should be able to do this by creating a calculated field similar to the following:

if sum([Actual])>=sum([Target]) then 'Target Met'

elseif sum([Actual])<sum([Target]) then 'Target Not Met' end

When this is placed on the view with the Milestone and Goals the correct output should appear. Hope this helps!

-Tracy

• ###### 2. Re: How to forecast future performance based on past actuals using multiple measures

Hi Tracy,

Thanks for the reply but I think you missed part of the problem that makes it tricky.   I'm not interested in comparing RowX.Target to RowX.Actual,  I want to compare RowX-1.Actual to RowX.Target.  I want to compare the previous measuremnet in time Actuals to the NEXT measurement in time Target.  Does this make sense?