HR Headcount by Effective Date and Status

[jwatt]

Hello!

I am trying to create a line graph to show our organization's FTE count over time. I assume I need some sort of measure to do this but so far any solutions I have found online and tried myself have not quite worked. I also need it to be more granular than just by month or year. 

My data includes multiple records per employee for whenever their status changes and HR enters the date it is effective.

I would like it to show over time the number of employees at each status. So one line each for Active, Terminated, Leave with Pay, Parental Leave, Retired. 

Here is an example of my dataset: 

ID	Eff_Date	c_Status	FTE
1300	7/1/2015	Active	1
1300	1/22/2021	Leave with Pay	1
1300	5/1/2021	Retired	1
3012	9/8/2020	Active	0.5
3012	6/16/2021	Terminated	0.5
8614	6/1/2015	Active	1
0407	7/1/2022	Active	0.75
0407	3/26/2023	LTD	0.75

I have actually been able to get this to work in a table visual using the following measure but it doesn't work in the line graph. 

Total FTE As At Selected Date = 
VAR SelectedDate =
    MAX ( 'DateTable'[Date] )
RETURN
SUMX (
    VALUES ( 'All'[ID] ),
    VAR LatestEffdt =
        CALCULATE (
            MAX ( 'All'[Eff_Date] ),
            FILTER (
                ALL ( 'All' ),
                'All'[ID] = EARLIER ( 'All'[ID] )
                    && 'All'[Eff_Date] <= SelectedDate
            )
        )
    RETURN
        CALCULATE (
            MAX ( 'All'[FTE] ),
            FILTER (
                'All',
                'All'[ID] = EARLIER ( 'All'[ID] )
                    && 'All'[Eff_Date] = LatestEffdt
            )
        )

I hope I have presented this clearly.  I have only recently started to use Power Bi for anything other than very simple visuals so any help or advice is greatly appreciated!

[Irwan]

hello @jwatt 

looking at your DAX, are you trying to find max FTE at latest date?

i might be misunderstood but please check if this accomodate your need.

create two measure for [Latest Date] and [Total FTE]

Latest Date =
CALCULATE(
    MAX('Table'[Eff_Date]),
    FILTER(
        ALL('Table'),
        'Table'[ID]=SELECTEDVALUE('Table'[ID])&&
        'Table'[Eff_Date]<=MAX('Table'[Eff_Date])
    )
)

Total FTE =
CALCULATE(
    MAX('Table'[FTE]),
    FILTER(
        ALL('Table'),
        'Table'[ID]=SELECTEDVALUE('Table'[ID])&&
        'Table'[Eff_Date]=[Latest Date]
    )
)

another thing, i assumed your DAX is a form of measure hence EARLIER does not work on measure. This probably cause your measure doesnt work. EARLIER can be used in calculated column so you might want to try in calculated column with EARLIER.

Hope this will help.

Thank you.

[jwatt]

Hi @Irwan 

Thanks for taking the time to look at this! 

For my data below, I could have explained better. Employee 1300 below for instance, is a full time employee (1 FTE) who was active on 7/1/2015 and went to leave with pay status on 1/22/2021 and then retired 5/1/2021. Also, I noticed you had ID 3012 as 5 and 0407 as 75 - not sure if it matters for the calculations you suggested but these are single employees who are part time so they are 0.5 and 0.75 FTE. 

We have many more employees than this since this is just a snippet. I have thousands of lines showing changes in c_Status over years.  What I would like to have it do is over time show how many employees are at each status. So for Leave with Pay, if in 2021 there was 1 ID with this status but then 2022 there were 4 and in 2023 there were 2 there would be a line representing this in the graph, along with separate lines for active, Parental Leave, etc. 

ID	Eff_Date	c_Status	FTE
1300	7/1/2015	Active	1
1300	1/22/2021	Leave with Pay	1
1300	5/1/2021	Retired	1
3012	9/8/2020	Active	0.5
3012	6/16/2021	Terminated	0.5
8614	6/1/2015	Active	1
0407	7/1/2022	Active	0.75
0407	3/26/2023	LTD	0.75

I did try to use your measures - my table name is "All."

Latest Date = 
CALCULATE(
    MAX('All'[Eff_Date]),
    FILTER(
        ALL('All'),
        'All'[ID]=SELECTEDVALUE('All'[ID])&&
        'All'[Eff_Date]<=MAX('All'[Dept_ID])
    )
)

Total FTE = 
CALCULATE(
    MAX('All'[FTE]),
    FILTER(
        ALL('All'),
        'All'[ID]=SELECTEDVALUE('All'[FTE])&&
        'All'[Eff_Date]=[Latest Date]
    )
)

When I added these to a line graph the same as in the pbix you provided, the graph just shows as blank. 

The DAX I provided in my original post has actually worked for a table visual. I had forgotten to copy it in but I did in fact have a measure similar to your Latest Date. Trying to use the measure in the line graph does not work because it creates different points for each effective date - for example if one person was hired on May 16, 2025 then the graph shows 1 person for that date where I need it to show the total number active on that date. 

Something like this - 

Please let me know if you have any further advice. 

Thank you!

[Irwan]

hello @jwatt 

what is FTE role in your headcount?

if you are looking count of employee with certain status, i think you can directly use line chart then put c_Status as legend and distinct count of employee ID.

or do you want only to see the latest c_Status of each ID?

Thank you.