Forum Discussion

aflora's avatar
aflora
Frequent Visitor
5 years ago
Solved

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!

 

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

 

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

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion

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

      • littlemojopuppy's avatar
        littlemojopuppy
        Community 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.