I have the following data on headcount.
|Name||Position||Start Date||End Date||Current Status|
|Joe Bloggsd||Head of Marketing||21/02/2017||Established|
|Noah Anyone||Head of Operations||21/06/2017||20/07/2019||Left|
|Joe Bloggs Jnr||VP Marketing||21/07/2018||20/01/2020||Transfer 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.
Hi @tobiasmcbride ,
You can refer to this blog:https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
@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 ])))
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?
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
We had a great 2022 with a ton of feature releases to help you drive a data culture.