Hello all,
I am newbie here and struggling to find a solution for an excel calculation in Pivot tables.
I have a simple pivot table with the source data having the information about Month, Project, Workstream, task name,Org ID, resource, planned hours, actual hours, planned cost and actual cost. There are three report filters Month, Project ID, Workstream ID on the pivot table.
I need to create two calculated fields 'Forecast at completion-hours' 'FAC - Cost', on the pivot table.
I am looking for two solution options.
1. based on the current month, the formula should pick actual up to current month and add the planned cost for rest of the year. e.g for june, FAC = actual upto may+planned from June to DEC.
2. If possible, create a drop down to allow the users to chose the month they want to see the FAC and use that month value to calculate the FAC.
Could you please advise me how to go about it? Any help is massively appreciated.
Attached is the sample file with data and the pivot table.
..Ram
Bookmarks