Count Lastest Records Before Selected Date

Hello community, 

I have the follwoing data model: Date (1-*) Employees (1-*) Events. 

Sample data from Employees:

In Power BI, I need to create measures such as Headcount, Terminations etc. Users must have the ability to select a Date in the slicer (and other slicers as well) and view these measures by them. The End of Month column from the below screenshot is pulled from the Date table dimension

I started with doing a general People Count (regardless of Employee Status),  but figured that if I select a date in my slicer (2024-02) for which I got no records in Employees, it will return blanks. 

People Count 1 = DISTINCTCOUNT( Employees[Employee ID] )

So to "bypass" this behaviour, I amended my formula. This helped me achieve my desired result of viewing the total people count by Month (from Date dim table):

People Count 2 = 
CALCULATE (
    DISTINCTCOUNT ( Employees[Employee ID] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] ) && 'Date'[Date] <> BLANK ()
    ),
    Employees[Employee Status] = "Active"
)

But then, as I moved forward with my report, I wanted to calculate Headcount, meaning only ACTIVE employee statuses.

Headcount =
CALCULATE (
DISTINCTCOUNT ( Employees[Employee ID] ),
FILTER (
ALL ( 'Date' ),
'Date'[Date] <= MAX ( 'Date'[Date] ) && 'Date'[Date] <> BLANK ()
),
Employees[Employee Status] = "Active"
)

But I got the same results like in the previous screenshot, and it`s normal. The code below is counting all the occurences of active Employee ID rows

I then figured that I need to make some adjustments. I need to be able to filter the rows of my table to display only the latest transactions up to my selected slicer date:. Here is an expected outcome:

So something that would allow me to select in my Slicer the Date (12/20/2024) and show People Count = 2. I then want to be able to refrence People Count in other measures for more advanced calculations (i.e. Headcount = CALCULATE( Peple Count, Employee Status="Active") and get the result 1 for 12/20/2024; i.e. Terminations = CALCULATE( Peple Count, 'Events'[Change Reason] = "Termination"); i.e. Levrage this further and calculate R12M Attrition).

The problem is getting this count correct 🙂 So, here`s what I did so far:

1. I started with creating a Snapshot Date, that`s basically the same value as my slicer selection:

Snapshot Date = CALCULATE( MAX('Date'[Date]), ALL('Date') )

2. Getting the Max Evemt Date 

Originally I did it like below, but it returned blank when I selected my date in the slicer (because employees has no records for 12/20/2024)

Max Event Date = 
CALCULATE (
    MAX ( Employees[Effective Start Date] ),
    FILTER( Employees , Employees[Employee ID] = MAX(Employees[Employee ID])))

So I adjusted to this:

Max Effective Start Date = 
CALCULATE (
    MAX ( Employees[Effective Start Date] ) ,
    ALLEXCEPT(Employees, Employees[Employee ID], Employees[Effective Start Date] ) 
)

3. I created a "Valid Record"field, which returns beautifully, like in my coloured screenshot above

Valid Record = IF([Max Effective Start Date]=[Latest Event Date In Scope],1,0)

4. Tried to do the People Count 3 (final) which... WENT WRONG, of course 🙂 

People Count Final = 
CALCULATE (
    DISTINCTCOUNT ( Employees[Employee ID] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] ) && 'Date'[Date] <> BLANK () && [Valid Record] = 1
    )
)

Morover, if I try to plot the End of Month (from Date) versus this measure, it`s returning 0!

Thank you for taking your time to review my case, any help is appreciated.