Add or subtract different columns based on date

[justin_chow]

Hello,

I need to add different columns based on the date that's inside another column.

Date of data	Person	Jan projected score	Feb projected score	Mar projected score	Jan actual score	Feb actual score	Mar actual score	Jan+Feb+Mar
Feb 1	Fred	10	20	30	40	50	60	??
Feb 1	Bob	1	2	3	4	5	6	??

So while we're in December, we add up Jan, Feb, and Mar's projected score. But after January passes, we add up Jan's actual score, and Feb+Mar's projected score. 

How do I set something up to automatically handle this?

[BrandonB · Accepted solution]

You could use an adaptation of this workflow. First the data is transposed so that the months become a column of data rather than headers. Then a formula tool is used to create actual date values from the month names. Then a filter tool is used to specify what the data is through so that actuals are pulled through for the dates prior and projected values are pulled through for months in the future. The summarize tool then adds these values together by Person. Finally, this data is joined back up to the original dataset. Workflow is attached. 

 

In this example, we assume that January and February have actuals and March is projected.