Total Count by Time Period

[TapZxK]

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) 

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)

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)

 

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

[amitchandak]

@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...

[TapZxK]

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

[amitchandak]

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

[TapZxK]

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. 

Best Regards,

Kris