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]

Save or share this article