Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Evaluate Headcount Table (SCD type II) for Trending Headcount

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
Anonymous
Not applicable
1 year ago

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.