How do I add custom, dynamic time period averages to Excel Pivot Table?

[Rob R]

I have an Excel worksheet that has Overtime (OT) data for each person in the company summarized per pay period. My objectives in Excel are as follows:


1. Show a current pay period summary of OT hours by department (sorted with most hours at top); If the solution is within the PivotTable it would be nice to ONLY show the current pay period (and applicable trend averages) if possible
2. Provide simple trend data (4 pay period AVG and YTD AVG) to make the current pay period data more relevant.
3. Make the analysis/tool/table easy to refresh by simply adding new rows to raw data; this will be provided to an HR admin so it must be user-friendly for updating every time period.




[Current Raw Data Screencap][1]
[1]:

[Current PivotTable Screencap][2] Note: Pay Period Numbers are columns.
[2]:

Ideally, the user could select the current pay period, and that would display the OT sum for the current pay period and the two averages mentioned above. From there, I would add conditional formatting to improve the UI for the managers/end users of the data.


Thanks in advance for any and all help!!

 

[Rob R]

Current Pivot Table Field List:

 

[Rob R]

I'm curious if anyone has insight on possibly using a Pivot Table Calculated Field to accomplish this. If I could ID the column of the current pay period and use the AVERAGE function with Current Week#[-1,-2,-3,-4], I could get my Four Period AVG. Perhaps I could do the same for YTD.

Is this type of formula (referencing a dynamic column selection) possible in a Calculated Field? Would it refresh properly when a new pay period (Week#) is added?