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,
I have created a report that shows headcount over time using measures, but when I adjust my date slicer, it recalculates the headcount for only the selected time period in the slicer (per measure). Rather, I would like for the headcount NOT to recalculate and see a snapshot of what headcount looked like at a point in time that I select. For example, I'd like to filter my data for 1/1/2018 through 11/30/2020 and have the bar chart show 1190 for 2018, 1107 for 2019, and 1029 for 2020.
I have attached two images. The first shows my date slicer including the entire date range (HCpicA). The second shows a smaller date range selected in the slicer (HCpicB) and you'll see a difference in the joiners/leavers/rehires/rehire terms/total HC between the two pics. How do I create something to keep the headcount totals from recalculating?
HCpicA:
HCpicB:
Since modifying the date slicer recalculates my measures, do I need a second date table that enables me to see a snapshot of different timeframes? Can other measures be created/used to achieve the result? Do I need to modify my measures? Help!
Thanks!
Alex
Solved! Go to Solution.
Hi!
Your headcount at any point in time is the sum of everyone hired, separated, rehired and re-separated. Your headcount calculation was only looking at people with one of those things happening during the selected date range. I added measures for running totals for each and changed the headcount calc to use those running totals. You can download the pbix here.
I also simplified your measures for hired, separated, rehired and re-separated...they were unnecessarily complex. You should also check them for accuracy.
Can you share the pbix using Google Drive or DropBox? can't download from your SharePoint...permissions error.
Give this a try...https://www.dropbox.com/s/tj0totccxwmqkuy/DummyHC_HR.pbix?dl=0
Hi!
Your headcount at any point in time is the sum of everyone hired, separated, rehired and re-separated. Your headcount calculation was only looking at people with one of those things happening during the selected date range. I added measures for running totals for each and changed the headcount calc to use those running totals. You can download the pbix here.
I also simplified your measures for hired, separated, rehired and re-separated...they were unnecessarily complex. You should also check them for accuracy.
Thank you so much for the quick help and simplification of my measures!
You're welcome! Glad I could help! 🙂
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |