Calculating cumulative head count?

[Jason2500]

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

01/01/2021

01/01/2020

-

02/01/2020

01/01/2020

01/03/2020

01/04/2021

I wanted to calculate cumulative headcount and wanted to visualize over the month 

I used below Dax measure

My data stored in a Headcount table

HC = COUNTROWS(Headcount)

TotalHC = CALCULATE( [HC], FILTER(ALL(Headcount[Hire Date]), Headcount[Hire Date] <= MAX(Headcount[Hire Date])))

Cumulative HC = CALCULATE([TotalHC], Headcount[Hire Date] > DATEVALUE("1/1/2020"), VALUES(Headcount[Hire Date]))

Above Dax, measures are not working

Kindly advise

[Ashish_Mathur]

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.

[Jihwan_Kim · Accepted solution]

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/