Employee count with changing records

[Anonymous]

Hi PBI community, 

I'm struggling with what I thought was easy lol.

First, I want you to know that have read multiple post and tried to figure what is different from my model, but I can not find what I'm doing wrong. 

Here is the problem: 

I need to generate the count of active employees at a selected month/date. In the data set there are multiple records per employee that represent changes in positions or locations; those are identified by different Record_Date

Here is a sample data

ID	Hire Date	Termination Date	Status_Code	Record Date
32617	2/4/2019		A	11/16/2021
32617	2/4/2019		A	9/20/2022
32617	2/4/2019		A	11/17/2022
32617	2/4/2019	1/12/2023	T	1/13/2023
32631	3/28/2011		A	7/22/2020
32631	3/28/2011		A	11/18/2020
32631	3/28/2011		A	6/9/2021
32631	3/28/2011		A	11/16/2021
32631	3/28/2011		L	2/23/2021
32631	3/28/2011	2/25/2022	T	2/26/2022
801974	10/15/2018		A	6/9/2021
801974	10/15/2018		A	11/16/2021
801974	10/15/2018		A	2/23/2021
801974	10/15/2018	11/3/2022	T	11/15/2022
801777	4/17/2017		A	4/20/2017
801777	4/17/2017		A	11/1/2019
801777	4/17/2017	9/1/2022	T	9/15/2022
801780	5/1/2017		A	6/1/2017
801780	5/1/2017		A	8/3/2020
801769	4/20/2017		A	4/20/2017
801769	4/20/2017		A	11/1/2019
801769	4/20/2017		A	9/15/2022
801768	4/3/2018		A	4/30/2018
801768	4/4/2018		A	5/30/2021
801767	3/29/2017		A	4/1/2017
801767	3/29/2017		A	9/2/2022

To use time intelligence, I joined Calendar table and the Employee table with inactive relationship in 'Hire Date' and 'Termination Date' with 'Date' from the calendar table.

The dax I'm using is as: 

Empl_Count_ = 
var maxdate = max('Calendar'[Date])
var empltotalcount =  CALCULATE(DISTINCTCOUNT('Employee'[ID]) , Employee[Status_Code] <> "T" )
var result = 
    CALCULATE(
        empltotalcount,
        REMOVEFILTERS('Calendar'),
        'Employee'[Hire Date] <= maxdate
        ,'Employee'[Termination Date] > maxdate || ISBLANK(Employee[Termination Date])
    )
Return
    result

Unfortunatelly the dax I'm using is counting all employees with out considering change in status.

This is the expected result: 

If select Dec 2022, Empl_Count = 5

If select Jan 2023, Empl_Count = 4

If select Aug 2022, Empl_Count = 7

If select full year 2022, Empl_Count = 8

TIA for all your help!

[Jihwan_Kim · Accepted solution]

Hi, 

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Active employee count measure: = 
VAR _latestrecorddate =
    GROUPBY (
        Employee,
        Employee[ID],
        "@latestrecord", MAXX ( CURRENTGROUP (), Employee[Record Date] )
    )
VAR _latestrecordinfo =
    CALCULATETABLE (
        Employee,
        TREATAS ( _latestrecorddate, Employee[ID], Employee[Record Date] )
    )
VAR _resulttable =
    FILTER (
        _latestrecordinfo,
        Employee[Hire Date] <= MAX ( 'Calendar'[Date] )
            && OR (
                Employee[Termination Date] = BLANK (),
                Employee[Termination Date] >= MIN ( 'Calendar'[Date] )
            )
    )
RETURN
    COUNTROWS ( _resulttable )

[Anonymous]

Hi Jihwan_Kim, 
What you provided is fantastic, thank you so much. 
Have another question: if want to count active employees based on the previous to the last 'Record Date', how will the measure you share will change?
Do need to sustract a month? sorry for the question, hope is not confusing. 

Thank again!