How to calculate employee headcount by department over a period of time with transfers
I have managed to calculate the headcount and actual number of staff by department over a period of time using starting date and leaving date using a calendar table. However, now i need to re-do the analysis which should also take into consideration the headcount of employees that have transfered to another department. Headcount will be the aggregate of new joiners Active Staff will be the Head count - leavers - transfers Lets say the table is Employees and has the following fields: a) Employee ID b) Date of joining c) Departmend joined d) Transfer date e) Department transfered to f) Leaving Date If employee A joined in January 2022, Department Finance, then the headcount of Department Finance should be incremented by 1 and active staff value should be incremented; If the employee A transfers to another department (Procurement) in August 2022 then the headcount of Department Finance should not be impact but the active staff should decreased by 1 and the headcount and active staff of department Procurement should be incremented by 1. In summary, need to monitor the headcount and active staff by department as changes to joiners and transfers and leavers happen over a period of time. Any help would be really appreciated.
Seeking DAX Help for Unique Employee Headcount Over Time with Dynamic Status Filtering
Hello Power BI Enthusiasts! I trust you're all doing wonderfully. I'm in a bit of a bind with a DAX measure I'm trying to construct in Power BI, and I could really use your collective expertise. My goal is to create a measure that counts unique employee records based on their employee number and filters these counts by their employment status using a related status dimension. This measure is intended to be dynamic, reflecting changes over time, leveraging a unique timestamp that marks each status change for the employees. Despite my efforts, I'm encountering an issue with duplicates in the data, Here is the DAX I'm using Test_Headcount_Dimstatus = VAR __selecteddate = MAX(Dim_calendar_start[Date]) VAR LatestEmployeeRecord = SUMMARIZE( 'EMPLOYEES - Ceridian modified', 'EMPLOYEES - Ceridian modified'[EMPLOYEE_EMPLOYEEID], "LatestTimestamp", MAX('EMPLOYEES - Ceridian modified'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP]) ) VAR FilteredEmployeeRecord = FILTER( LatestEmployeeRecord, [LatestTimestamp] <= __selecteddate ) RETURN COUNTROWS(FilteredEmployeeRecord) Accompanying this post is a snapshot of my model for reference. I've been grappling with this problem for over a month, and it's quite frustrating. Does anyone have any insights into what might be going wrong? Any suggestions or guidance would be greatly appreciated. Thank you for taking the time to help!Need help with a DAX measure (Headcount)
Hello everyone! I'm excited to make my first post on this forum and to be a part of the Power BI community. I've been exploring Power BI for about a year and am encountering a challenge with the following formula: Headcount Status = VAR SelectedDate = MAX('DateDimensionTable'[Date]) VAR LatestStatusPerEmployee = CALCULATETABLE( SUMMARIZE( /*to summarize by employee ID*/ 'FactsTable', 'FactsTable'[EMPLOYEE_EMPLOYEEID], "LatestStatusChange", MAX('FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP]), "LatestStatus", CALCULATE(MAX('FactsTable'[EMPLOYMENTSTATUS_LONGNAME]), /*is the column where the status are*/ USERELATIONSHIP('DateDimensionTable'[Date], 'FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP]), FILTER(ALL('FactsTable'), 'FactsTable'[EMPLOYEE_EMPLOYEEID] = EARLIER('FactsTable'[EMPLOYEE_EMPLOYEEID]) && 'FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP] == [LatestStatusChange])) ), USERELATIONSHIP('DateDimensionTable'[Date], 'FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP]) ) VAR CountCondition = FILTER( LatestStatusPerEmployee, [LatestStatus] IN {"Status 1", "status2", "status3"} ) RETURN COUNTROWS(CountCondition) My objective is to create a dynamic headcount measure that tracks employee status changes over time. For instance, using a slicer, if I filter to December 31st, an employee who was in "Status 1" in January and moved to "Status 2" in December should be reflected correctly in the breakdown. However, I'm facing two main issues: The overall headcount seems incorrect and tends to decrease as I go back in time, even though there should be variations. In visualizations, employee statuses aren't updating correctly. For example, if an employee's status changed in August, that status remains fixed in all subsequent views. In my model, the primary connection is between the date in my dimension table and the original hire date in the fact table. I've attempted to use a USERELATIONSHIP in the DAX to incorporate a link with the status creation timestamp, but this hasn't resolved the issue. I'm open to any questions or suggestions for further clarification. Thank you in advance for your help!
Continuous Headcount by Hire and Termination Date
Hello, I've been racking my brains trying to get dynamic headcount by day that takes into account their hire and termination dates and only counts if they are between them. I'm either lookin for day by day or a monthly one (any hire or termination within that month counts as an headcount for the end of month total). I've been able to get a static end of month one but not a dynamic one in the past. It just tends to show the Delta of hire to terminated for each month.Rolling 12 Month Headcount Sum as of Start of Year
Hi all, I am stuck once again! My goal is to create a Rolling 12 Month Headcount Sum measure as of the start of the year. For example, headcount as of 1/1/2021 was 2,690. I want to apply this to every month in 2021 and then sum for those 12 months resulting in a total of 32,280. To complicate this a little bit more here is another example. Since it is February 2022, I would want to calculate a sum of the last 12 months knowing that the start of year headcount was 2,690 in 2021 and is now 3,007 in 2022. The expected measure would sum 2 months of 2022 at 3,007 headcount and 10 months of 2021 at 2,690 for a total of 32,914. I hope this makes sense as I have a difficult time trying to describe this. Here is a table of my data: Year Month Start of Year Headcount Rolling 12 Month Headcount Sum Expected Result 2021 Jan 2,690 2021 Feb 2,690 2021 Mar 2,690 2021 Apr 2,690 2021 May 2,690 2021 Jun 2,690 2021 Jul 2,690 2021 Aug 2,690 2021 Sept 2,690 2021 Oct 2,690 2021 Nov 2,690 2021 Dec 2,690 2,690 32,280 2022 Jan 3,007 3,007 32,597 2022 Feb 3,007 3,007 32,914 Here is the DAX for Start of Year Headcount and Rolling 12 Month Headcount Sum: Start of Year Headcount = CALCULATE ( COUNTROWS ( Headcount ), STARTOFYEAR ( 'Date'[Date] ) ) Rolling 12 Month Headcount Sum = CALCULATE ( [Start of Year Headcount], DATESBETWEEN ( 'Date'[Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) ), LASTDATE ( 'Date'[Date] ) ) ) Thanks in advance for your time and efforts! Please let me know if I can supply any additional information that may be helpful.HR issue with DAX - first occurence of an employee after filtering
Hello, I have this table with employees. It has a relation with Calendar table. My task is to create a measure that shows new hires first occurances on the date axis (edit: I want to show first occurance of the employee). Now it's just a simple DAX: Hires = DISTINCTCOUNT('Employee'[Name]). So we have such a table in PBI. How to write this DAX so that: - there is only one occurence ( 01/01/2019) when no filter is applied; - when there's a filter Level=Mid there's a new hire an occurence only on 01/01/2019 and when there's a filter Level=Senior there's a new hire one only on 01/01/2021; - when there's a filter Team=Bob there's a new hire only one occurence on 01/08/2021 ? I'm stuck. Please, help.
