Forum Discussion

Jason2500's avatar
Jason2500
Helper I
5 years ago
Solved

Calculating cumulative head count?

Hi   I am having headcount data, which has a target date. The target date is the hire date of the employees   The target date looks like below   Target Date   01/01/2020 02/01/2021 ...
Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Jason2500 

    I am not quite sure how the outcome looks like.

    I tried to create a sample pbix file by myself, and please check whether it is what you are looking for.

    All measures are in the sample pbix file. (Link down below).

    Or, please draw visualizations of your desired outcome. It will be much more helpful for me to come closer.

     

     

    https://www.dropbox.com/s/pe4cxx6u1uis7a4/jason.pbix?dl=0 

     

     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

    Linkedin: https://www.linkedin.com/in/jihwankim1975/

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

Ensure you have a Calendar Table with calculated column formulas for Year and Month Name and Month Number.  Sort the Month name by the month number.  Build a relationship from the Hire Date column to the Date column of the Calendar Table.  Build 2 slicers for Year and Month from the Calendar Table.  Select any one Year and Month.  Write these measures:

HC = COUNTROWS(Headcount)

Cumulative HC = CALCULATE([TotalHC],datesytd(Calendar[Date],"31/12"))

Hope this helps.