Calculate Active Employees with only 1 Date Column

[PatLam0187]

Hello,

I am trying to calculate for the total number of Active employees and YoY growth.

The challenge I am facing is that my data is not commonly arranged arranged as I am using just 1 date column.

The data is structured like this and is connected to a Date Dimension table called [DateDim]

Employee ID	Status	Begin Date	Action
1001	Active	2022-06-20	New Hire
1001	Active	2023-10-27	Leave of Absence
1001	Active	2024-12-04	Return to Work
1001	Terminated	2025-01-13	Termination
1001	Active	2022-06-05	Rehire
1002	Active	2022-06-20	New Hire
1003	Active	2025-02-10	New Hire
1004	Active	2025-06-01	New Hire
1005	Active	2022-05-20	New Hire
1005	Active	2023-02-20	Change in Position
1005	Terminated	2025-05-15	Termination

Thank you

[v-lgarikapat]

Hi @PatLam0187 ,

Thanks for reaching out to the Microsoft fabric community forum.

@ryan_mayu ,

@SamsonTruong ,

Thanks for your prompt response

I have implemented the required logic and uploaded a sample PBIX file for your review. Kindly have a look and let me know if any adjustments or additional changes are needed.

Looking forward to your feedback.

If this post helped resolve your issue, please consider the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana.

[ryan_mayu]

@PatLam0187 

will 1001 and 1002 be counted as active for year 2023 and beyond?

[PatLam0187]

@ryan_mayu Yes, it should be a cummulative count. Say I am filetring as of today, those that were active 2022 to present should be also counted as active. Hope that makes sense. Thank you.

[SamsonTruong]

Hi @PatLam0187 ,

This can be achieved with 2 measures.

The first measure would be to calculate the total number of Active employees and would be like so:

ActiveEmployees = 
VAR SelectedDate = MAX('DateDim'[Date])
VAR LatestActive =
    ADDCOLUMNS(
        SUMMARIZE(
            'EmployeeActions',
            'EmployeeActions'[EmployeeID],
            "LatestActionDate",
                CALCULATE(
                    MAX('EmployeeActions'[BeginDate]),
                    'EmployeeActions'[BeginDate]<=SelectedDate
                )
        ),
        "LatestActionStatus",
            CALCULATE(
                MAX('EmployeeActions'[Status]),
                FILTER(
                    'EmployeeActions',
                    'EmployeeActions'[EmployeeID]=EARLIER('EmployeeActions'[EmployeeID])
                    && 'EmployeeActions'[BeginDate]=EARLIER([LatestActionDate])
                )
            )
    )
RETURN
    COUNTROWS(
        FILTER(LatestActive,[LatestActionStatus]="Active")
    )

We can then use a second measure like below to calculate the YoY growth:

ActiveEmployeesYoY = 
VAR CurrentDate = MAX('DateDim'[Date])
VAR PrevYearDate = EDATE(CurrentDate,-12)
VAR CurrentValue =
    CALCULATE([ActiveEmployees],'DateDim'[Date]=CurrentDate)
VAR PrevYearValue =
    CALCULATE([ActiveEmployees],'DateDim'[Date]=PrevYearDate)
RETURN
    DIVIDE(CurrentValue-PrevYearValue,PrevYearValue)

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session

[PatLam0187]

Thank you, @SamsonTruong however this only counts those who were active as of date. I also need to include those who were active in the previous dates. Say someone is Active in 2023, that person should also be inlcuded in todays count. Thanks again.