Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Super User
Super User

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/

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


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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
Super User
Super User

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/

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


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.