Sales pipeline - Pivot table?

[JBK123]

Hi guys,

I am trying to create sales projections by customer over the next few years and I am currently stuck.

Example Data below:

Client name	Salesman name	Revenue per month	Contract open	Contract Close
Customer A	Salesman A	2,000	March 2018	December 2018
Customer B	Salesman A	2,500	May 2018	December 2018

<tbody>
</tbody>

I want to create a pivot table which would look like the below but I want to keep my original data set as basic as possible. I realise this is unlikely but hopefully I am missing something. The reason I want to use a pivot table is because I want to add slicers and timelines to analyse the sales projections quickly.

Client Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Grand Total
Customer A			2,000	2,000	2,000	2,000	2,000	2,000	2,000	2,000	2,000	2,000	20,000
Customer B					2,500	2,500	2,500	2,500	2,500	2,500	2,500	2,500	20,000

<tbody>
</tbody>

Thank you for any help!

 

[ranman256]

that should work,
drag Client to Row labels
drag ContractOpen to column labels
Revenue in Sum

 

[JBK123]

Using this method I get 2,000 in March and 2,500 in May rather than the continuous revenue in the months following.

There must be a work around, I just can't think of one.