Creating a chart for rolling figure in date format
Hi all, I have built a employee turnover metric that looks at average headcount and total leavers in a 12 month timeframe. To get the % figures I have built a Date Table that when I slide the start and end period of each month it returns me the complete breakdown of the data I need. Rolling Turnover % (Total) = DIVIDE([Rolling Leavers (Total)],[Avg Monthly Headcount (12M)],0) However, when reporting on trends it makes it difficult as I have to move the date table periods each time to the 12 month outlook I need. For example for December 2024 I need to put 01/01/2024 - 31/12/2024, for January 2025 it would be 01/02/2024 - 31/01/2025. The Date Table is linked to my main staff file, which has each month of the period 01/2024, 02/2024 etc, and then linked also to my exits table that has the last day per individual. This gives me exactly what I need but it would be very helpful to try and investigate if there is a way to add to this to be able to build some charts over time to better understand trends. Appreciate I may be lacking a lot of info still required, will try my best to share anything that is not clear. Kind regards,
Calculating Active Employees from Most Recent Record
Hello, I am trying to analyze workforce metrics over time. I have a fact table that has a record for each time an employee file has changed. It has at least one record for every employee. This table includes unique employee ID, Start date, division, Term Date, Effective Date (when the record was created) and a calculated column of Record Order- which numbers the rows for each employee, with 1 being the first record, 2 being the second and so on. I also have dimension tables for Date, Org Structure (Division/Section) Demographics I need to count active employees at a point in time where Start date <= SelectedDate, TermDate is blank, or > SelectedDate. The issue I am running into is; if an employee has been active in two divisions at different times during the selected period, they are Distinct Counted in each division. This means that the overall organization numbers are accurate, BUT they will count as an employee in each division when that slicer is applied. I need to be able to use just the most recent record for each unique employee- as that most recent record will show their current location. Example Data: PS ID start date Division Term Date RecordOrder Eff Date 1 1/1/2020 A null 1 5/18/2020 1 1/1/2020 A null 2 5/19/2020 1 1/1/2020 B null 3 7/11/2020 1 1/1/2020 B null 4 10/3/2020 2 1/2/2020 A 4/2/2021 1 1/2/2020 2 1/2/2020 A 4/2/2021 2 10/3/2020 3 1/1/2020 A null 1 1/1/2020 3 1/1/2020 B null 2 10/3/2020 In the above data set, using a date slicer on say 10/4/2020, I would want to return a distinct count (3) active employees, 2 in division B. 1 1/1/2020 B null 4 10/3/2020 2 1/2/2020 A 4/2/2021 2 10/3/2020 3 1/1/2020 B null 2 10/3/2020 The first approach I took was to use an 'events in progress' methodology 1. Measure to count all employees based on Start Date 2. Measure to distinct count all active employees at point in time where start date<= selected date, and term date is blank or after Selected date this returns accurate counts for the whole organization- but when appling division slicers, it counts employees as a member of every division they had been active in at any point. So, if someone was in A and later B- they would show up when slicing by A or B. I need to return only the most recent record. The next approach I tried was to identify the MaxRecordOrder of each ID, and filter the active employees measure where RecordOrder=MaxRecord Order: MaxRecordOrder = CALCULATE( MAX(Employee_Fact[RecordOrder]), ALLEXCEPT(Employee_Fact, Employee_Fact[PS ID], 'DateTable') ) Active Employees= VAR EndDatePerVisual = MAX('DateTable'[Date]) VAR RESULT = CALCULATE( DISTINCTCOUNT(Employee_Fact[PS ID]), REMOVEFILTERS('DateTable'), FILTER( Employee_Fact, Employee_Fact[Start Date] <= EndDatePerVisual && ( Employee_Fact[Term Date] > EndDatePerVisual || ISBLANK(Employee_Fact[Term Date]) ) && Employee_Fact[RecordOrder] = [MaxRecordOrder] ) ) RETURN RESULT This approach seems to correctly identify the max RecordOrder for each ID, responsive to the date slicer- but still counts rows where the MaxRecord order <> RecordOrder- so employees are still showing up in counts for multiple divisions! I've also attempted the second approach using the max effective date in place of the RecordOrder calculated column, with no success. Any help would be appreciated!
