How To Apply Slicer to Most Recent Effective Date

[Anonymous]

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!

[Ashish_Mathur]

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]))))

[SamWiseOwl]

Hi @Anonymous 

Mine are on the visuals and it seems fine, what is happening on yours?

[SamWiseOwl]

I tried it with a bigger data set and it appears to work on all months:

[Anonymous]

Ah! I realized I forgot to apply an active vs non-active filter. Once I did that, your solution worked perfectly. Thank you!

[SamWiseOwl · Accepted solution]

Hi @Anonymous 

Apply a Top N 1 Effective date filter to the indivdual visuals or to the entire page:

[Anonymous]

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?