cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jackson_smith25
Frequent Visitor

Displaying Company Headcount across a line chart from transaction table data

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 IDEmployee StatusEffective DateRegionWork Location
1Active1/1/20NorthA
2Active1/1/20NorthB
3Active1/1/20SouthC
4Active1/1/20SouthC
1Active2/4/20NorthA
3Terminated2/8/20SouthC
4Active3/4/20NorthA
3Active4/6/20SouthC
5Active5/1/20NorthA

 

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

4 REPLIES 4
jackson_smith25
Frequent Visitor

@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
 
Annotation 2020-06-23 165902.jpg
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
Community Support
Community Support

@jackson_smith25 ,

 

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

jackson_smith25
Frequent Visitor

@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/204
3/1/203
4/1/204
5/1/204

 

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
Community Support
Community Support

@jackson_smith25 ,

 

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors