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!