”;
In the previous chapter, you have learnt how to create base Headcount measures – i.e. Actual Total Headcount, Budget Total Headcount, and Forecast Total Headcount.
In this chapter, you will learn how to create Year-Over-Year Headcount measures and how you can analyze the data with these measures.
Creating Year-over-Year Actual Ending Headcount Measure
You can create Year-over-Year Actual Ending Headcount Measure as follows −
YoY Actual Ending Headcount:=[Actual Ending Head Count]-[Prior Year Actual Ending Headcount]
Creating Year-over-Year Actual Average Headcount Measure
You can create Year-over-Year Actual Average Headcount Measure as follows −
YoY Actual Average Headcount:= [Actual Average Headcount]-[Prior Year Actual Average Headcount]
Creating Year-over-Year Actual Total Headcount Measure
You can create Year-over-Year Actual Total Headcount Measure as follows −
YoY Actual Total Headcount:=[Actual Total Head Count]-[Prior Year Actual Total Headcount]
Analyzing Data with Year-over-Year Actual Headcount Measures
Create a Power PivotTable as follows −
-
Add the fields Fiscal Quarter and Month from the Date table to Rows.
-
Add the measures – Actual Ending Head Count, Prior Year Actual Ending Head Count, YoY Actual Ending Head Count to Values.
-
Insert a Slicer on the field Fiscal Year.
-
Select FY2016 in the Slicer.
Create another Power PivotTable on the same worksheet as follows −
-
Add the fields Fiscal Quarter and Month from the Date table to Rows.
-
Add the measures – Actual Average Head Count, Prior Year Actual Average Head Count, YoY Actual Average Head Count to Values.
Connect the Slicer to this PivotTable as follows −
- Click the Slicer.
- Click the Options tab under Slicer Tools on the Ribbon.
- Click Report Connections.
Report Connections dialog box appears.
- Select the above two PivotTables.
- Click OK.
Creating Year-over-Year Budget Ending Headcount Measure
You can create Year-over-Year Budget Ending Headcount Measure as follows −
YoY Budget Ending Headcount:= [Budget Ending Head Count]-[Prior Year Actual Ending Headcount]
Creating Year-over-Year Budget Average Headcount Measure
You can create Year-over-Year Budget Average Headcount Measure as follows −
YoY Budget Average Headcount:= [Budget Average Headcount]-[Prior Year Actual Average Headcount]
Creating Year-over-Year Budget Total Headcount Measure
You can create Year-over-Year Budget Total Headcount Measure as follows −
YoY Budget Total Headcount:=[Budget Total Headcount]-[Prior Year Actual Total Headcount]
Creating Year-over-Year Forecast Ending Headcount Measure
You can create Year-over-Year Forecast Ending Headcount Measure as follows −
YoY Forecast Ending Headcount:= [Forecast Ending Head Count]-[Prior Year Actual Ending Headcount]
Creating Year-over-Year Forecast Average Headcount Measure
You can create Year-over-Year Forecast Average Headcount Measure as follows −
YoY Forecast Average Headcount:= [Forecast Average Headcount]-[Prior Year Actual Average Headcount]
Creating Year-over-Year Forecast Total Headcount Measure
You can create Year-over-Year Forecast Total Headcount Measure as follows −
YoY Forecast Total Headcount:=[Forecast Total Headcount]-[Prior Year Actual Total Headcount]
”;