cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jason2500
Helper I
Helper I

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
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
 
 
 
1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

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.

 

Picture4.png

 

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/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User III
Super User III

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Jihwan_Kim
Community Champion
Community Champion

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.

 

Picture4.png

 

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/

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors