Displaying Company Headcount across a line chart from transaction table data

[Anonymous]

Hi All,

I've been working on an HR Report for my company.  We've had a hangup trying to display the headcount at our company over time across a line chart.  We'd also like to be able to filter this line chart by region and work location.

I mentioned the problem in this article, but was not able to get the DAX to function properly.  We had to sideline this report for a bit due to COVID reporting, but are back to working on it again.

Our data comes from a transaction table.  Every time a change happens to an employee (new manager, salary change, divisonal change), they get a new row added to the table.  We've currently narrowed down a table that has a rolling count of the past 4 years, and we'd like to display this information as a line chart.  It is currently around 44,000 rows in the table.

Employee ID	Employee Status	Effective Date	Region	Work Location
1	Active	1/1/20	North	A
2	Active	1/1/20	North	B
3	Active	1/1/20	South	C
4	Active	1/1/20	South	C
1	Active	2/4/20	North	A
3	Terminated	2/8/20	South	C
4	Active	3/4/20	North	A
3	Active	4/6/20	South	C
5	Active	5/1/20	North	A

We would also be happy with rolling these into monthly totals, so that we could display the headcount for the 1st of each month on a line chart.

Here's the DAX I currently have (which is not working):

Thanks in advance,

JS

[Anonymous]

@v-yuta-msft ,

Here is the DAX I am using:

Headcount Result =

CALCULATE(

COUNTROWS('JobTxns From 2016'),

FILTER(

ALLEXCEPT('JobTxns From 2016','JobTxns From 2016'[Effective Date].[Month]),

'JobTxns From 2016'[Employee Status Code] = "A"

&& DAY('JobTxns From 2016'[Effective Date]) = 1

)

)

It is currently not doing what I want it to

It appears to only be giving me one value for every day.  This number should be changing month to month (even day to day if possible), based on each employee's most recent transaction to the day it is evaluating for. (i.e. if an employee has a status code on March 2019 and March 2020, the value in May 2019 should be using the status code from March 2019).

Any thoughts on how to do this?

-JS

[v-yuta-msft]

@Anonymous ,

You may create a measure using dax like pattern below:

Result =
CALCULATE (
    COUNTROWS ( Table ),
    FILTER (
        ALLEXCEPT ( Table, Table[Effective Date].[Month] ),
        Table[Employee Status] = "Active"
            && DAY ( Table[Effective Date] ) = 1
    )
)

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft ,

Thanks for your response.

For total headcount based on the table above, we would want to plot a line chart off of data like this:

1/1/20	4
2/1/20	4
3/1/20	3
4/1/20	4
5/1/20	4

I'm essentially looking for a way to calculate the total active employees for the first date of each month, and then from there I would be able to plot that on a line chart and track the trends over time.

-JS

[v-yuta-msft]

@Anonymous ,

I have read the post in your link. You said "Do you have any ideas on how we would be able to show this/ restructure the data to be able to visualize the trend of our total heacount over time?" Could you please clarify more details about this requirement? If possible, please share the expected result.

Regards,

Jimmy Tao