Identify Latest Events and Count

[dianacmaria]

Hello community,

I got the below sample data. My original database has ~30 columns, but I included only 2 (since I`ll need to be able to slice my result by them).

I also got a Date dimension table with a (1-many) relationship from Date[Date] to Employees[Effective Start Date] and an Event table with (many-1) relationship from Events[Key - Employee ID & Effective Start Date to Employees[Key - Employee ID & Effective Start Date]

The Employees table will never have same day records, but it can have multiple records in the same month.

Effective Start Date	Employee ID	Employee Status	Labour Type
12/31/2023	51115216	Active	Indirect Labor
1/31/2024	51115216	Active	Indirect Labor
4/30/2024	51115216	Active	Indirect Labor
3/31/2025	51115216	Paid Leave	Indirect Labor
4/30/2025	51115216	Terminated	Indirect Labor
12/31/2023	92999434	Active	Indirect Labor
12/31/2023	92999434	Unpaid Leave	Indirect Labor
1/31/2024	92999434	Unpaid Leave	Indirect Labor
6/30/2025	92999434	Terminated	Indirect Labor
3/31/2025	94062306	Active	Indirect Labor
5/31/2025	94062306	Active	Indirect Labor
8/31/2025	94062306	Terminated	Indirect Labor
7/31/2025	97001779	Active	Direct Labor
7/31/2025	97001779	Active	Indirect Labor

Okay, so I will have a date slicer from Date table (Year, Month and Date in a slicer). Users will be able to select more than one value in this slicer.

So I got two measures:

Snapshot Date = MAX('Date'[Date])
Baseline Date = MIN('Date'[Date])

This is an Employees database, so I`ll need to calculate the Headcount, Termination and Hires at the selected point in time.

So I start off with an overall People Count:

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

The reason why I do it like this is because some Employee IDs will not have values for some months. But if a user selects a date, regardless data exists or not, I need to view the People Count for it.

Okay, all looks nice until here, but the real fun only now begins.

While my People Count retrieves a count of employees for each month, it is not correct. I figured I needed to count the distinct employee IDs from the latest records that are before the Snapshot Date (which is selected in a slicer by users). Below I have a screenshot, where for demonstration purposes I included Effective Start Date, Employee ID, as well as two columns,

So looking at this screenshot, assuming that an User selected Date = 12/20/2024 in the Date slicer, the only rows counted are for employee 51115216 as of 4/1/2024 and employee 92999434 as of 1/1/2024. As per the screenshot, they`ll show as 1 each and 2 in total.

If let`s say I add a bar chart and I put on the X-axis the Employee Status, the slicer date selection remains 12/20/2024, I should see 1 active, 1 unpaid leave.

If I add the Labor Type on X-axis, I see 2 Indirect Labor.

If I add any other dimension, it will count accordingly.

I`ve tried multiple things out: I calculated a Max Event Date as explained here, but I figured that if I select a Date in the slicer for which my Employees do not have Effective Start Dates for, it will return Blank.

I ended up with this long formula:

Test = 
var SelectedDate = MAX( 'Date'[Date] )
var CurrentEmployee = MAX(Employees[Employee ID] )
var MaxEffectiveDate = MAXX(
    FILTER(Employees, Employees[Employee ID] = CurrentEmployee && Employees[Effective Start Date] <= SelectedDate),
    Employees[Effective Start Date]
)
VAR MaxAll =
    MAXX (
        SUMMARIZE (
            VALUES ( Employees[Employee ID] ),               -- keeps your slicers
            Employees[Employee ID],
            "LatestDate",
                CALCULATE (
                    MAX ( Employees[Effective Start Date] ),
                    FILTER (
                        ALL ( Employees[Effective Start Date] ),
                        Employees[Effective Start Date] <= SelectedDate
                    )
                )
        ),
        [LatestDate]
    )

RETURN
CALCULATE( DISTINCTCOUNT( Employees[Employee ID] ), FILTER(Employees, MaxAll = MaxEffectiveDate ) )

But to no avail… I even tried ranking, no success. Please can someone help me? I`ve been struggling for days with this.

[ankit]

Hi @dianacmaria - Thanks for the detailed explanations. Request you to please share a sample PBIX file to look into this. Can keep only required columns and tables.

Thanks
Ankit J

[ankit]

Hi @dianacmaria - Can you share the sample PBIX file else will close this by Monday.

Thanks
Ankit J

[allen.bernier17]

Looks like you’re really close. The logic makes sense, but you’ll probably get cleaner results using TOPN inside CALCULATETABLE to grab the latest effective date per employee before the selected date, then count distinct IDs from that. Sharing a small PBIX will definitely help fine-tune the DAX

[ankit]

Hi @dianacmaria - Marking this as closed as no response is received from your side.

[HufferD]

@dianacmaria,

Thanks for putting so much effort into describing the problem you’re facing. You’ll discover that if you also add a sample .pbix file, you’ll get an answer quicker. If you don’t, it forces users to create it themselves and, like in this case, add a date table and wire it all up. That’s a burden that most people don’t want to take on.

At any rate, I think you’re trying to count employees whose latest status change (as of a selected date) occurred within a specific time period. Based on your example, when filtering to December 2023, you want to see the 2 employees whose most recent event as of that date was in December 2023.

Here’s a measure that should accomplish this:

As-of Headcount by Latest Event = 
VAR MaxDate = MAX('Date'[Date])
VAR MinDate = MIN('Date'[Date])
RETURN
COUNTROWS(
    FILTER(
        ALL(Employees),
        VAR LatestEventDate = 
            CALCULATE(
                MAX(Employees[Effective Start Date]),
                ALLEXCEPT(Employees, Employees[Employee ID]),
                Employees[Effective Start Date] <= MaxDate
            )
        RETURN
        LatestEventDate = Employees[Effective Start Date]
            && LatestEventDate >= MinDate
            && LatestEventDate <= MaxDate
    )
)

For each employee, [As-of Headcount by Latest Event] finds their latest event date as of the maximum date in the filter context. It then checks whether that latest event date falls within your selected date range. Then it counts distinct employees meeting these criteria.

Here’s an example using your sample data filtered to March 2025:

Let me know if that solves your problem.