Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I've been working on an HR Report for my company. We've had a hangup trying to display the headcount at our company over time across a line chart. We'd also like to be able to filter this line chart by region and work location.
I mentioned the problem in this article, but was not able to get the DAX to function properly. We had to sideline this report for a bit due to COVID reporting, but are back to working on it again.
Our data comes from a transaction table. Every time a change happens to an employee (new manager, salary change, divisonal change), they get a new row added to the table. We've currently narrowed down a table that has a rolling count of the past 4 years, and we'd like to display this information as a line chart. It is currently around 44,000 rows in the table.
Employee ID | Employee Status | Effective Date | Region | Work Location |
1 | Active | 1/1/20 | North | A |
2 | Active | 1/1/20 | North | B |
3 | Active | 1/1/20 | South | C |
4 | Active | 1/1/20 | South | C |
1 | Active | 2/4/20 | North | A |
3 | Terminated | 2/8/20 | South | C |
4 | Active | 3/4/20 | North | A |
3 | Active | 4/6/20 | South | C |
5 | Active | 5/1/20 | North | A |
We would also be happy with rolling these into monthly totals, so that we could display the headcount for the 1st of each month on a line chart.
Here's the DAX I currently have (which is not working):
Thanks in advance,
JS
Here is the DAX I am using:
@Anonymous ,
You may create a measure using dax like pattern below:
Result =
CALCULATE (
COUNTROWS ( Table ),
FILTER (
ALLEXCEPT ( Table, Table[Effective Date].[Month] ),
Table[Employee Status] = "Active"
&& DAY ( Table[Effective Date] ) = 1
)
)
Regards,
Jimmy Tao
Thanks for your response.
For total headcount based on the table above, we would want to plot a line chart off of data like this:
1/1/20 | 4 |
2/1/20 | 4 |
3/1/20 | 3 |
4/1/20 | 4 |
5/1/20 | 4 |
I'm essentially looking for a way to calculate the total active employees for the first date of each month, and then from there I would be able to plot that on a line chart and track the trends over time.
-JS
@Anonymous ,
I have read the post in your link. You said "Do you have any ideas on how we would be able to show this/ restructure the data to be able to visualize the trend of our total heacount over time?" Could you please clarify more details about this requirement? If possible, please share the expected result.
Regards,
Jimmy Tao
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |