Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Community,
I'm not sure if there's an easy fix for this one (or a fix at all), but I'm hoping you all can help me troubleshoot an issue I'm running into.
We had to do some unconventional methods to deliver data from our HRIS system to our data warehouse (Snowflake). Instead of an API integration, we are basically sending snapshot data to Snowflake on a weekly basis. This snapshot data includes an effective date as of the day the data was sent to Snowflake. So, when data is sent on 8/1, it has an effective date of 8/1/2024; data on 8/8 has a date of 8/8/2024; and so on and so forth. Data is being appended to the warehouse report, rather than being replaced.
In my Power BI dashboard, I have a cards that show headcount data - all employees, regular employees, temps, and contractors. I also have a line graph that shows trending data month-over-month. In order for our leaders to view data at a point in time, I included a slicer that's based off a calendar table, and the leaders can select a month to see what their headcount breakdown was during a certain month.
Here's my issue: my slicer is not applying to the most recent effective date. Instead, it's combining all data points within that month. So, using the example data I'm including:
8/1/2024 - 3 active regular employees; 2 active temp employees, and 3 active contractors.
8/8/2024 - 2 active regular employees; 1 active temp employee, and 2 active contractors.
What I want to happen: when I apply the slicer, I want the cards to tell me the numbers as of 8/8/2024
- 2 active regular employees
- 1 active temp
- 2 active contractors
Instead, what's actually happening: when I apply the slicer to August, all data points are being combined, so I'm getting data on 8/1 and 8/8 being added together and getting:
- 5 active regular employees
- 3 active temps
- 5 active contractor
Is there a way for me to configure the slicer to only pull data as of the most recent effective date?
Here's the example data:
Effective Date | Employee ID | Employee Type | Employment Status |
7/31/2024 | 123456 | Regular | Active |
7/31/2024 | 234567 | Regular | Active |
7/31/2024 | 345678 | Regular | Active |
7/31/2024 | 456789 | Temp | Active |
7/31/2024 | 567890 | Temp | Active |
7/31/2024 | 678901 | Contractor | Active |
7/31/2024 | 789012 | Contractor | Active |
7/31/2024 | 890123 | Contractor | Active |
8/1/2024 | 123456 | Regular | Active |
8/1/2024 | 234567 | Regular | Active |
8/1/2024 | 345678 | Regular | Active |
8/1/2024 | 456789 | Temp | Active |
8/1/2024 | 567890 | Temp | Active |
8/1/2024 | 678901 | Contractor | Active |
8/1/2024 | 789012 | Contractor | Active |
8/1/2024 | 890123 | Contractor | Active |
8/8/2024 | 123456 | Regular | Active |
8/8/2024 | 234567 | Regular | Inactive |
8/8/2024 | 345678 | Regular | Active |
8/8/2024 | 456789 | Temp | Active |
8/8/2024 | 567890 | Temp | Inactive |
8/8/2024 | 678901 | Contractor | Active |
8/8/2024 | 789012 | Contractor | Active |
8/8/2024 | 890123 | Contractor | Inactive |
And here's the pbix file.
Please let me know if you need additional information. Thank you!
Solved! Go to Solution.
Hi @DanielG1987
Apply a Top N 1 Effective date filter to the indivdual visuals or to the entire page:
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Hi,
Write this measure and drag it to the 4 card visuals
Measure = CALCULATE(COUNTA('Worker Data'[Employee ID]),LASTNONBLANK('Calendar'[Date],CALCULATE(COUNTA('Worker Data'[Employee ID]))))
Hi @DanielG1987
Mine are on the visuals and it seems fine, what is happening on yours?
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
I tried it with a bigger data set and it appears to work on all months:
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Ah! I realized I forgot to apply an active vs non-active filter. Once I did that, your solution worked perfectly. Thank you!
Hi @DanielG1987
Apply a Top N 1 Effective date filter to the indivdual visuals or to the entire page:
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Thanks, @SamWiseOwl !
This solved the problem for the current month (August), but when I tried to go back to July, it broke the cards again. I imagine this would be the case going forward if we tried to go back to a previous month, and that this solution would only work for the current month - is that correct?
Any additional thoughts?