Calculating Total Employees each Month
Hi All, Looking to create a measure which tells me which employees are present at a certain month and year. I have a "Persons" Table with the following fields: Full Name, First Name, Last Name, Start Date, End Date. I have a "Calendar" table with the following fields: Date, Month, Year, YearMonth. Calendar is linked to Persons via an Active One to Many Relationship to Start Date. And a non-active relationship between date and End Date. How I can calculate how many staff at present at each month and year using this information. As the result I'm getting at the moment are only returning data for the month where people have a start date. For Example Aiden Sally's Count should appear from Jun 2018 - March 2023. For Example Yuliia Susan's Count should appear from Jun 2022 - March 2023.
Count staff that move between roles
Hi All, I am new to Power BI and I am struggling to create a measure that counts staff of different categories on a particular date. My model is as below: The 'Events' table records include a staff number, post ID, and a date on which that staff member attained that post ID. Each post ID corresponds to a fleet, rank, and status in the 'Post' table. Staff Number Post ID Date 123456 2 09/05/2022 234567 15 14/05/2022 I am trying to count the current number of pilots for each fleet, rank & status, but I cannot work out how to count only the most recent post ID for each pilot. I know that a row-specific context is required to check each staff number, but I am unsure how to confirm a record includes the most recent date in the table for that specific staff number (ie their current post). Here is my DAX code so far: Active Staff = CALCULATE ( COUNTROWS ( Events ), FILTER ( ALLSELECTED ( Events ), Events[Staff Number] = Events[Staff Number] && Events[Date] = MAX ( Events[Date] ) ) ) And here is the output with my actual dataset. There are 238 rows in my 'Events' table whilst there are 202 discrete staff numbers, so I feel like I'm getting close, I just need help to fix the context of the date & staff number comparison. Any advice or guidance would be greatly appreciated! Kind regards, James
Show % Employee Turnover - by month, with option to see by department, location, company
Hi, I'm pretty new to PowerBI and need help setting up an employee turnover dashboard for HR. Requirements are to be able to see employee headcount history to date and employee turnover by: location, department and company. My table data is set out as follows: Employee ID | Full Name | Company | Department | Location | Job Role | Type | Date | Staff Type = Entry / Exit to inform the Date column Staff = 1 for each employee Please could some please help advise the best way i could add an additional turnover column to this table (below) as a %. The usual calculation used in HR is: Total leavers / average employees in that time period. Many thanks!DAX optimisation row-wise calculation
Hello guys, need some help. I have to calculate listed employee number - its average employee number. I have calculated two measures: Employee count = VAR total = CALCULATE( DISTINCTCOUNT(Employee[No_]); FILTER(ALL('Calendar'); 'Calendar'[Date]<=MAX('Calendar'[Date]));USERELATIONSHIP('Calendar'[Date];Employee[Data_Joined])) VAR fired = CALCULATE( COUNTA(Employee[Data_Left]); FILTER(ALL('Calendar'); 'Calendar'[Date]<=MAX('Calendar'[Date]));USERELATIONSHIP(Employee[Data_Joined];'Calendar'[Date])) RETURN IFERROR(total-fired;BLANK()) Listed employee = AVERAGEX('Calendar';Employee[Employee count]) The calculation is ok, but it took quite a long time with uploading to the table. With new Performance Analyze it shows that it took 79096 ms for the table to upload Please, can anyone help me to improve performance? Pbix file (I can't make active relationships because of additional tables that I use in production) https://drive.google.com/open?id=17AXwVhnTcrHZNjZUsDensXxsQ8VusJRz
