Forum Discussion
Need help with headcount trend reporting
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:
HCpicA
HCpicB:
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
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.
5 Replies
- littlemojopuppyCommunity Champion
Can you share the pbix using Google Drive or DropBox? can't download from your SharePoint...permissions error.
- afloraFrequent Visitor
Give this a try...https://www.dropbox.com/s/tj0totccxwmqkuy/DummyHC_HR.pbix?dl=0
- littlemojopuppyCommunity Champion
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.