Hello,
My team has been trying to figure this one out for a while, but any solutions we find for similar situations don't quite work for this dataset. We're all a bit new to Power BI so perhaps (hoping!) there is an easy solution.
We have a dataset including ID, Name, Eff_Date, Pay_Status, and FTE (and other fields we need to include for context, but those are the fields necessary for our analysis) for all current and historical employees. Each employee has several rows of data - any time someone's status, FTE, salary, etc change, there is a new record with a new effective date. There is a huge variation in dates since employee details change all throughout the year.
Our goal is a table that will show the FTE totals and how many people are active vs on leave (Pay_Status) when any given date is selected. For example, if an employee had 3 records, 01-JAN-2023, 01-JAN-2024, and 01-JAN-2025 and the date 01-FEB-2024 was entered into the slicer, I would only want to see the 01-JAN-2024 record. It does of course need to do this for all employees so we can say, for instance, on 01-FEB-2024, we had 125 FTE, 10 on leave, 115 active and be able to filter to another timepoint to see the same information on that date.
If when I add the Eff_Date to the table and change it to show Latest Eff_Date and choose a date on the slicer, it shows the latest record and still includes prior records where one of our key fields has changed. So for the scenario above, if the employee was 0.5 FTE on 01-JAN-2023 and became 1.0 FTE on 01-JAN-2024, it pulls both of those records still. I need only the latest record even if other key fields have changed.
We have tried using other solutions but they all seem to include using fields that don't have an equivalent in our dataset or aren't trying to accomplish the exact same goal.
If anyone could assist, we would be very appreciative! 🙂
