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!

[v-sdhruv]

Hi @jwatt ,
Since we didnt hear back, we would be closing this thread.
If you need any assistance, feel free to reach out by creating a new post.

Thank you for using Microsoft Community Forum

[v-sdhruv]

Hi @jwatt ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions, please feel free to reach out.
Thank You

[v-sdhruv]

Hi @jwatt ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions, please feel free to reach out.We would be  happy to help with any further assistance you may need.
Thank You

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

[jwatt]

Hi @Irwan 

The total FTE at each c_Status is what I would like to see by date rather than count of employee. The graph you provided for the Total FTE by Eff_Date is what I want but split out by c_Status - which I can do by adding c_Status to the legend. 

Unfortunately when I use the measures you provided in my own file it does not seem to work. 

In my previous reply, I provided the measures as written in my project with the actual table name I am using ("All") and provided a screenshot showing that the graph shows no information. 

Do you see any issue with how I have copied your measures over and replacing the table name with "All"? Or do you have any further ideas on why it might not be working for me? I have replicated your file but just with changing the table name so I am not sure why the graph wouldn't show anything.

Thanks very much!

[Irwan]

hello @jwatt 

i dont see any problem with your DAX.
Replacing table name with "ALL" should be any problem as far as i know.

however if you suspect on that table name, try changing the name and see if it makes any difference.

the blank value means the measures couldnt find any value. surely there is an issue that prevent the measure to work but kinda hard to imagine the issue from description.

can you share your pbix? upload it in dropbox or googledrive then open access permission.

remove any confidential information.

if you have doubt put your file in public post, you can send private message for the dropbox link.

Thank you.

[Irwan · Accepted solution]

hello @jwatt 

using your sample pbix, i can find the value using the exact same DAX.

however, you need to have a ID selection because of same ID DAX.

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

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

if you will provide ID slicer, then you can remove that line since slicer selection will do the same as finding same ID.

Then you can have a line chart even without an ID selection. However, the value will be shown is max value of FTE.

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

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

Hope this will help.

Thank you.