- Data Modeling with DAX Tutorial
- Home
- Data Modeling with DAX - Overview
- Data Modeling with DAX - Concepts
- Data Modeling Using Excel Power Pivot
- Loading Data into the Data Model
- Defining Data Types in the Data Model
- Understanding Data Tables
- Extending the Data Model
- Base Finance Measures & Analysis
- YoY Finance Measures & Analysis
- Variance Measures & Analysis
- Year-to-Date Measures & Analysis
- Quarter-to-Date Measures & Analysis
- Budget Measures & Analysis
- Forecast Measures & Analysis
- Count of Months Measures
- Ending Headcount Measures
- Average Headcount Measures
- Total Headcount Measures
- YoY Headcount Measures & Analysis
- Variance Headcount Measures
- Cost Per Headcount Measures & Analysis
- Rate Variance & Volume Variance
- Data Modeling with DAX Resources
- Quick Guide
- Useful Resources
- Discussion
Ending Headcount Measures
You can create Ending Headcount measures for a specific period of time. The Ending Headcount is the sum of the people as on the last date in the specified period for which we have a non-blank sum of people.
The Ending Headcount is obtained as follows −
For a Month − Sum of People at the end of the specific Month.
For a Quarter − Sum of People at the end of the last Month of the specific Quarter.
For a Year − Sum of People at the end of the last Month of the specific Year.
Creating Actual Ending Headcount Measure
You can create Actual Ending Headcount measure as follows −
Actual Ending Head Count:=CALCULATE(SUM('Finance Data'[Actual People]),LASTNONBLANK('Finance Data'[Date], IF(CALCULATE(SUM('Finance Data'[Actual People]), ALL(Accounts))=0, BLANK(), CALCULATE(SUM('Finance Data'[Actual People]), ALL(Accounts)))), ALL(Accounts))
DAX LASTNONBLANK function as used above returns the last date for which you have a non-blank sum of people so that you can calculate the sum of people on that date.
Creating Budget Ending Headcount Measure
You can create Budget Ending Headcount measure as follows −
Budget Ending Head Count: = CALCULATE(SUM('Finance Data'[Budget People]),LASTNONBLANK('Finance Data'[Date], IF(CALCULATE(SUM('Finance Data'[Budget People]), ALL(Accounts))=0, BLANK(), CALCULATE(SUM('Finance Data'[Budget People]), ALL(Accounts)))), ALL(Accounts))
Creating Forecast Ending Headcount Measure
You can create Forecast Ending Headcount measure as follows −
Forecast Ending Head Count:= CALCULATE(SUM('Finance Data'[Forecast People]), LASTNONBLANK('Finance Data'[Date], IF(CALCULATE(SUM('Finance Data'[Forecast People]), ALL(Accounts))=0, BLANK(),CALCULATE(SUM('Finance Data'[Forecast People]), ALL(Accounts)))), ALL(Accounts))
Creating Prior Year Actual Ending Headcount Measuree
You can create Prior Year Actual Ending Headcount measure as follows −
Prior Year Actual Ending Headcount:=CALCULATE('Finance Data'[Actual Ending Head Count], DATEADD('Date'[Date],-1,YEAR))
Analyzing Data with Ending Headcount Measures
Create a Power PivotTable as follows −
Add the fields Fiscal Year and Month from the Date table to Rows.
Add the measures Actual Ending Headcount, Budget Ending Headcount, Forecast Ending Headcount, Prior Year Actual Ending Headcount from Finance Data table to Values.
Insert a Slicer on the Fiscal Year field.
Select FY2016 in the Slicer.
