Need help with headcount trend reporting

[aflora]

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!

https://aligntech-my.sharepoint.com/:u:/p/aflora/EWAH79bCRptDi8esxEFOjiYBJVWY2BSXLboTbhSpCzE3Iw?e=4j... 

Thanks!

Alex

[littlemojopuppy]

Can you share the pbix using Google Drive or DropBox?  can't download from your SharePoint...permissions error.

[aflora]

Give this a try...https://www.dropbox.com/s/tj0totccxwmqkuy/DummyHC_HR.pbix?dl=0 

[littlemojopuppy · Accepted 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.

[aflora]

Thank you so much for the quick help and simplification of my measures!

[littlemojopuppy]

You're welcome!  Glad I could help!  🙂