"Trendline" in Pivot

[#1]

Hello,

I have Pivot table as shown in attachment. It has two expressions =sum(TOT) (total sales so far) and =sum(BUD) (sales budget). Dimension on the top is months.

1) Now I would like to have TOT sums for the rest of the months which are not ready on the basis of previous months. In the expression what I'm looking after is that I would count Full months sales so far divide by the amount of monts and the I would have sales per month which I could use in the total sales. This is accurate enough. In the chart I would also like to these numbers which are counted based on estimation to have another color so they would be noticed. One illustration is shown below.

1	2	3	4	5	6	7	8	9	10	11	12	Tot
2	3	2	3	2	3	2	3	2	2,44	2,44	2,44	29,33

2) And another newbie question is that I have total sums now on the right side Total TOT and BUD, how can I have another column on the end to have calculation  TOT minus BUD?

[#2]

Looks okay, that DIFF solution at the end is just fine.

[JonnyPoole]

Ok great.  Is your post answered ?   If yes please close the  post marking any helpful or correct answers .

[#4]

And I'm new to this forum also. But have you posted that latest and right solution (28th day) example file with the DIFF only at the end and multiple years..?

[JonnyPoole]

The XLS data set that was provided did not have multiple years. 

Do you want to update / repost it with a sample that matches your situation. Then i can check it against what was done to see if it causes problems ?

[#6]

For sure. I already tested and of course it summed up the amount and when 2013 was chosen it tried to calculate the forecast for that year also.

[JonnyPoole · Accepted solution]

Just rereading this thread.  I didn't upload the last sample so apologies for that.

Here i added multiyear support. 

I made an assumption that a user would only look at 1 year at a time when viewing this chart so i set it up that way.

take a look and test it out.