Headcount with only Start Dates and End Dates

[HRAnalyst]

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

[HRAnalyst]

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! 

[ERD]

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.

[eliasayyy]

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 

[ERD]

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.

[eliasayyy]

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

 

[yyzheng12]

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