cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tobiasmcbride
Helper III
Helper III

Finding numbers of employees at particular date

Hi,

 

I have the following data on headcount.

 

NamePositionStart DateEnd DateCurrent Status
Joe BloggsdHead of Marketing21/02/2017 Established
Noah AnyoneHead of Operations21/06/201720/07/2019Left
Joe Bloggs JnrVP Marketing21/07/201820/01/2020Transfer Out

 

I want to be able to calculate the number of employees we had at a particular date. This would ideally be one filter that I could select a date from and it would give me the total number of employees at that date, that includes those who are currently established, who had just started, who have since left etc. I.e. just a date filter that enables me to say - So at 31st March 2019 how many people did we have in the business, where were they and what were they doing?

 

Easy enough for those estabslihed but need to include those who have since left but were established employees at that time.

 

Many thanks.

6 REPLIES 6
V-lianl-msft
Community Support
Community Support

Hi @tobiasmcbride ,

 

You can refer to this blog:https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr... 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Mariusz
Community Champion
Community Champion

Hi @tobiasmcbride 

 

Please see the attached file with a solution that creates an extra table in Power Query that expands your dates.

image.png

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

az38
Community Champion
Community Champion

Hi @tobiasmcbride 

it depends on you data model

it will be like

Measure = 
CALCULATE(Countrows('Headcount'); FILTER(ALL('Headcount'); 'Headcount'[StartDate] <= SELECTEDVALUE([Date]) && (ISBLANK('Headcount'[EndDate]) || 'Headcount'[EndDate] >= SELECTEDVALUE([Date]))

the best practice is to create a calendar table like 

CalendarTable = CALENDARAUTO()

and then dd a measure above


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Hi,

 

Still can't get this @az38 to work - can you check the formula again and assist?

@tobiasmcbride 

@V-lianl-msft has suggested the solution, which should fit in your required. In case you do not have a join between strat date and date table, then try the second formula

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) 
				&& (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date])))
				,(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))
				
Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) 
				&& (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date])))
				,(Employee[Employee Id ])))

 



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

OK so when I try that it states that the function "selected value' has been used in a true/false expression that is used as a table filter expressxion. this is not allowed?

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.