Evaluate Headcount Table (SCD type II) for Trending Headcount

[Anonymous]

Hi Team, 

Here's a snippet of three tables I have, one is a basic calendar table, with date, endofweek, and endofmonth and the second on is a employee table that's a slow changing dimension type II table, meaning each time there's a 'transaction' with an employee a new record gets inserted into the table. There's a relationship between the two yellow columns. The last table, circled in blue, is the result I'm struggling with. 

My challenge is I'm trying to evaluate when an employee was 'active' over time, on the last day of every month, while taking in considersation their status, and count them 'active' for that month. 

Example if you start to look at the table in the middle, this employees career started in Oct 2022 and then it was terminated on May 2023, then they were rehired back on the following month in June and they're 'active' until the most recent record in Oct 2024. In this example this employee would be counted 'active' at the end of every month from Oct 2022 to Oct 2024, except when they were terminated in May 2023. My DAX measure isn't counting them correctly as you can see in the table on the right. Below is my current measure I'm using, and I can't seem to figure out the solution. 

Trending Headcount = 

VAR MaxDate = MAX('Date'[EndOfMonth])
RETURN
CALCULATE(
    DISTINCTCOUNT(Emp[EMP_ID]),  
    Emp[EFF_TMSTP] <= MaxDate && Emp[EMP_STATUS] = "Active")

Any help with this measure would be greatly appreciated and happy to provide more context if needed. 

[powerbiexpert22]

Hi @Anonymous ,

please try below , please see below pbix for your reference

https://drive.google.com/file/d/1HVSc9j7yGE1vVXxvZBNf2HpFaeQrTi4z/view?usp=drive_link

Trending Headcount =

CALCULATE(

    COUNTROWS(employee),FILTER(ALLEXCEPT(employee,employee[endofmonthemp]),

    employee[eff_tmstp]<=max(employee[endofmonthemp]) && employee[emp_status]="active"))

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result clearly.

[Anonymous]

Hi @Anonymous ,

Based on the description, try using the following DAX formula.

Trending Headcount = 
VAR MaxDate = MAX('Date'[EndOfMonth])
RETURN
CALCULATE(
    DISTINCTCOUNT(Emp[EMP_ID]),
    FILTER(
        Emp,
        Emp[EFF_TMSTP] <= MaxDate &&
        (
            Emp[EMP_STATUS] = "Active" ||
            (
                Emp[EMP_STATUS] = "Terminated" &&
                Emp[EFF_TMSTP] > MaxDate
            )
        )
    )
)

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous , 

Thanks for taking a stab at this challenge, used your DAX measure and didn't quite turn out what I was looking for, if you could see in the snapshot below. 

Great that it counts the EMP_ID once per month, like on 07/2023, the employee had multiple records all of which were active and so counting them once for the month is exactly the desired outcome. Not so great, in the highlighted parts of the middle table we can see the employee was active in 05/31/2023, 09/30/2023, and 11/30/2023, but on the output table on the right, it's not shown. Playing with the measure now and trying to figure this out, any help would be greatly appreciated, also uploading the data so it can be copied into excel as a previous user requested. Thanks again.