If you work with human resources data, you might want to show your organization’s headcount for the latest month. This can help you track the growth or decline of your workforce over time and identify any trends or patterns. But how do you do that in Power BI? How do you filter your data to show only the latest month? How do you create a visual showing the headcount by department, location, or other dimension?
In this article, I will walk you through the steps of creating a simple report that displays the headcount data for the latest month. Whether you are a beginner or a Microsoft power bi consultant, this article is useful.
What You Need
To follow along with this tutorial, you will need:
- A Power BI account. You can sign up for a free trial here.
- A data source that contains employee information, such as name, department, location, hire date, termination date, etc. You can use your own data or download a sample dataset here.
- Basic knowledge of Power BI. If you are new to Power BI, you can check out this beginner’s guide.
Step 1: Load and Transform Your Data
The first step is to load and transform your data in Power BI. To do that, follow these steps:
- Open Power BI Desktop and click on Get data in the ribbon.
- Select Excel and browse to the location of your data source file. Click Open.
- In the Navigator pane, select the sheet that contains your employee data and click Load.
- In the Power Query Editor, you can apply some transformations to clean and prepare your data for analysis. For example, you can:
- Rename columns to make them more descriptive.
- Change data types to match the values in each column.
- Remove any unnecessary columns or rows.
- Create calculated columns to derive new information from existing columns.
- Click Close & Apply to save your changes and load your data into Power BI.
Step 2: Create a Date Table
The next step is creating a date table to help us filter our data by month.
A date table is a table that contains one row for each date in a range, along with some additional columns that describe the date, such as year, month, quarter, day of the week, etc.
To create a date table in Power BI, follow these steps:
- In the Home tab of the ribbon, click on Enter Data.
- In the Create Table dialog box, enter Date as the name of the column and click OK.
- In the Power Query Editor, click on Add Column in the ribbon and select Date > From Date Range.
- In the From Date Range dialog box, enter your date range’s start and end dates. For example, if you want to cover the last 12 months, you can enter 01/01/2023 as the start date and 12/31/2023 as the end date. Click OK.
- You should see a new column called Date with one row for each date in your range. You can now add more columns to describe each date. For example, you can:
- Click on Add Column > Date > Year to add a column with the year of each date.
- Click on Add Column > Date > Month > Name of Month to add a column with the name of each month.
- Click on Add Column > Date > Quarter of Year to add a column with the quarter of each date.
- Click on Add Column > Date > Day of Week Name to add a column with the name of each day of the week.
- Rename your table as DateTable and click Close & Apply.
Step 3: Create Relationships Between Tables
The third step is to create relationships between your tables. Relationships are links that tell Power BI how to join your tables based on common columns. Relationships enable you to slice and dice your data across different dimensions.
To create relationships between your tables in Power BI, follow these steps:
- In the left pane of Power BI Desktop, click on Model to switch to the model view.
- In the model view, you should see your tables as boxes with their columns as fields. Drag and drop the Date column from the DateTable to the Hire Date column from the Employee table. This will create a one-to-many relationship between the two tables based on the date columns.
- Similarly, drag and drop the Date column from the DateTable to the Termination Date column from the Employee table. This will create another one-to-many relationship between the two tables based on the date columns.
- You should see two arrows pointing from the DateTable to the Employee table, indicating the relationships. You can double-click on each arrow to edit the properties of each relationship, such as cardinality, direction, and cross-filter direction.
Step 4: Create Measures to Calculate Headcount
The fourth step is to create measures to calculate your organization’s headcount for each month. Measures are calculations that are performed on your data based on the context of your report, such as filters, slicers, or visuals.
To create measures in Power BI, follow these steps:
- In the Power BI Desktop’s left pane, click Report to switch to the report view.
- In the report view, right-click on the Employee table in the Fields pane and select New Measure.
In the formula bar, enter the following formula and press Enter:
Headcount Start =
CALCULATE(
DISTINCTCOUNT(Employee[Employee ID]),
USERELATIONSHIP(DateTable[Date], Employee[Hire Date]),
FILTER(
ALL(DateTable),
DateTable[Date] <= MAX(DateTable[Date])
)
)
This measure will calculate the number of distinct employees who were hired before or on each date in the date table.
Similarly, right-click on the Employee table and select New Measure. Enter the following formula and press Enter:
Headcount End =
CALCULATE(
DISTINCTCOUNT(Employee[Employee ID]),
USERELATIONSHIP(DateTable[Date], Employee[Termination Date]),
FILTER(
ALL(DateTable),
DateTable[Date] >= MIN(DateTable[Date])
)
)
This measure will calculate the number of distinct employees who were terminated after or on each date in the date table.
Finally, right-click on the Employee table and select New Measure. Enter the following formula and press Enter:
Headcount =
[Headcount Start] – [Headcount End]
This measure will calculate the difference between the headcount start and headcount end measures, which will give us the headcount for each date in the date table.
Step 5: Create a Visual to Show Headcount Data
The final step is to create a visual showing the latest month’s headcount data. To do that, follow these steps:
- In the report view, click on Insert in the ribbon and select Card. This will create a blank card visual on your canvas.
- Drag and drop the Headcount measure from the Employee table to the card visual. This will show the total headcount for all dates in your data.
- To filter the card visual by the latest month, click on Filters in the ribbon and select Relative date filtering.
- In the filter pane, select DateTable[Date] as the field to filter by. Select is in this month as the filter type. Click Apply filter.
- You should see that your card visual now shows only the headcount for the latest month in your data. You can format your card visually by changing its title, font size, color, etc.
To show more details about your headcount data, you can create other visuals, such as a line chart that shows the trend of headcount over time or a matrix that shows the headcount by department and location. To do that, you can use any of the other visuals available in Power BI and drag and drop your fields and measures accordingly.
