topic Re: Headcount with only Start Dates and End Dates in DAX Commands and Tips

Headcount with only Start Dates and End Dates

HRAnalyst — Wed, 26 Jul 2023 13:47:39 GMT

I am a new user of PowerBI but other threads do not seem to assist with my exact searches.

I have an overall employee data set that splits our organisation by Org, Department, Teams, Job Level etc - I then have start dates and end dates for all employees. Similar to below:

Employee Name	Organisation	Department	Team	Job Level	Job Title	Start Date	End Date
Bob Smith	Doodles	Marketing	Social Media	4	Lead Social Media Organiser	08/02/2022
Clare Smith	Doodles	Marketing	Social Media	5	Assistant Social Media Organiser	01/03/2022	03/02/2022

I am trying to run a power query from our HR system so that each month I can report on our current headcount. All posts that I have come across so far show only reports which already have a headcount report that is being generated - would anyone be able to assist?

I've tried a few calculations from other posts which do not seem to return anything when I start putting the measure into visuals - I have a full date table in my PowerBI which I have tried methods of both linked and unlinked to the data set and it still returns blank. Any assistance would be great.

Thanks

Re: Headcount with only Start Dates and End Dates

yyzheng12 — Wed, 26 Jul 2023 14:39:51 GMT

What does your table diagram look like? It all depends on your data structure.

Re: Headcount with only Start Dates and End Dates

eliasayyy — Wed, 26 Jul 2023 14:44:23 GMT

im not sure but lets say you have a calendar table and just start date and end date

create the emasure

headcount = VAR _r = SUMMARIZE( 'Table', 'Table'[Employee Name], "left?", IF( MAX('Table'[End Date]) <> BLANK() && MONTH(MAX('CALENDAR'[Date])) >= MONTH(MAX('Table'[End Date])) , "Yes" , "No"), "started?", IF( MONTH(MAX('Table'[Start Date])) <= MONTH(MAX('CALENDAR'[Date])) , "Yes" , "No") ) VAR _t = ADDCOLUMNS(_r,"Cond" , IF( [left?] = "No" && [started?] = "Yes" , 1,0)) return COUNTROWS(FILTER(_t, [Cond] = 1))

end result

Re: Headcount with only Start Dates and End Dates

ERD — Wed, 26 Jul 2023 17:44:17 GMT

Hi HRAnalyst ,

You can use this measure:

Measure = CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Start Date] <= MAX ( 'Date'[Date] ), 'Table'[End Date] >= MIN ( 'Date'[Date] ) || ISBLANK ( 'Table'[End Date] ) )

I assume you have a Date table not connected to the Data table.

Re: Headcount with only Start Dates and End Dates

eliasayyy — Thu, 27 Jul 2023 07:00:27 GMT

hello ERD its a smaller measure but it doesnt count it correctly on the month they left , so if you select march 2022 you will have 2 headcount instead of one

Re: Headcount with only Start Dates and End Dates

ERD — Thu, 27 Jul 2023 07:13:10 GMT

So you don't want to count the month they left? Because from your first screenshot I see 3 people, as one of them was working 2 days in March.

Re: Headcount with only Start Dates and End Dates

HRAnalyst — Thu, 27 Jul 2023 10:50:52 GMT

Current Employees =

CALCULATE(COUNTX
( FILTER( 'Employee Information','Employee Information'[Start Date]<=MAX(Dates[Date] )

&& (ISBLANK('Employee Information'[Last Day of Employment] )

|| 'Employee Information'[Last Day of Employment]>MAX(Dates[Date] ) ) ),

('Employee Information'[First Name] ) ),
CROSSFILTER('Employee Information'[Start Date],Dates[Date],None))

I ended up using the above which seemed to have done the job - thanks for the help below to get to this!