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

Total Count by Time Period

Hello, 

 

I'm working on a headcount dashboard and I have run in to a problem. 

There is a fact table that contains information about people such as their names their start date, planned end date, confirmed end date, status, etc. 

 

I have linked this fact table with my dates table. From Dates.Date to Resource Start Date (See ScreenShot) Relationship.jpg

 

I have created a measure that tells me how many active people we have right now. 

 

Total Active =
CALCULATE(COUNTA('Resource Data'[Enterprise ID]),
FILTER('Resource Data', 'Resource Data'[Resource Lifecycle Status]="Active Resource"||'Resource Data'[Resource Lifecycle Status] = "Roll-Off Scheduled"),
FILTER('Resource Data', 'Resource Data'[Org Unit Level 6] = "Agile" || 'Resource Data'[Org Unit Level 6] = "DevOps"))
 
as well as a measure for Confirmed Growth
 
Confirmed Growth =
CALCULATE(COUNTA('Resource Data'[Enterprise ID]),
FILTER('Resource Data', 'Resource Data'[Resource Lifecycle Status]="Active Resource"),
FILTER('Resource Data', 'Resource Data'[Org Unit Level 6] = "Agile" || 'Resource Data'[Org Unit Level 6] = "DevOps"))
 
and a measure for Planned Growth.
 
Planned Growth =
CALCULATE(COUNTA('Resource Data'[Enterprise ID]),
FILTER('Resource Data', 'Resource Data'[Resource Lifecycle Status]="Roll-On Scheduled" || 'Resource Data'[Resource Lifecycle Status]="Roll-On Scheduled (longterm - >30 days in future)"),
FILTER('Resource Data', 'Resource Data'[Org Unit Level 6] = "Agile" || 'Resource Data'[Org Unit Level 6] = "DevOps"))
 
and I'm getting what I want from the visualization.  (See Screenshot)Growth and Planned Growth.jpg
 
 
I also want to see how many Active people we had per month, how many we had in Jan, Feb, Marc, etc. 
 
When I simply add "Total Active" Measure on to a vertical bar chart with my months i'm not getting what I want. (See screenshot)
Active by month.jpg 
 
I think what it shows is how many people started within given month not how many active we had in that month. as for example in February the number of total active should have been close to 140 and not 7
 
I would appreciate your help.
 
Best Regards,
Kris

 

4 REPLIES 4
Super User IV
Super User IV

Re: Total Count by Time Period

@TapZxK 

I have a blog on a similar topic, see if that can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


TapZxK Helper I
Helper I

Re: Total Count by Time Period

Thanks @amitchandak , 
It helped a lot with userelationship topic however I wasn't able to correctly re-created numbers of active employees following your formula. the numbers appear to be off. not sure what could be the problem. 

 

BR,

Kris

Super User IV
Super User IV

Re: Total Count by Time Period

@TapZxK , Can you share sample data and sample output.





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


TapZxK Helper I
Helper I

Re: Total Count by Time Period

Hi @amitchandak ,

 

Please find attached sample data here

 

A bit more information on the sample data:

I'm only interestd in people who are in Org Unit lvl 6 = Agile and DevOps ignoring the rest. 

My Measure for calculating Total Active is below. It counts Enterprise ID's based on the fact that a person is in the Org Unit Level 6 = Agile and DevOps and that their Status in Resource Roster is = Active Resource and Roll-Off Scheduled. 

It returns a value of 152 Active Employees right now. and 152 is correct. 

What I want to see is how many Active Employees I had in the past in  each month / quarter

 

Total Active =
CALCULATE(COUNTA('Resource Data'[Enterprise ID]),
USERELATIONSHIP('Resource Data'[Resource Start Date], Dates[Date]),
FILTER('Resource Data', 'Resource Data'[Resource Lifecycle Status]="Active Resource"||'Resource Data'[Resource Lifecycle Status] = "Roll-Off Scheduled"),
FILTER('Resource Data', 'Resource Data'[Org Unit Level 6] = "Agile" || 'Resource Data'[Org Unit Level 6] = "DevOps"))
 
What I want to see is a similiar graph like below. (Please Ignore the Delta to Target and Target Line) only Green Bar is relevant. 
I have a dedicated "Dates" Table that have the Fiscal Quarters for me. 
 
Example ScreenShot.jpg
 
End Date Shot.jpgStart Date Shot.jpg
 
 
 
 
Best Regards,
Kris
 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors