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.

[PatLam0187]

Hello @v-lgarikapat, the update you had provided is working when the filter/date slicer is not selected if I select 2025 for example it will not include the 2022 record and will only count who are active beggining 2025. It should include the active 2022 record. Thank you so much!

[v-lgarikapat]

Hi @PatLam0187 ,

Thank you for the follow-up question.
I have revised the measure and uploaded the updated PBIX file.

Kindly review it and let me know if any adjustments or additional changes are needed.

Looking forward to your feedback.

Best regards,
Lakshmi Narayana

[PatLam0187]

Hello @v-lgarikapat ,

The solution needs one last fine tuning I think.

The terminated profile in May is still counted as Active when I add a month slicer or a week slicer.

Let's say someone is terminated May 5, 2025. That record should be counted as Active in June or May 6, 2025 onwards.

Thank you so much for your help!

[v-lgarikapat]

Hi @PatLam0187 ,

Thanks for the follow-up question. Could you please try the modified measure below?

CumulativeActiveEmployees_v3 =
VAR SelectedDate =
MAX ( 'DateDim'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'EmployeeDetails'[Employee ID] ),
FILTER (
ADDCOLUMNS (
SUMMARIZE ( 'EmployeeDetails', 'EmployeeDetails'[Employee ID] ),
"@LatestStatus",
VAR LatestRecord =
CALCULATETABLE (
TOPN (
1,
FILTER (
'EmployeeDetails',
'EmployeeDetails'[Begin Date] <= SelectedDate
&& 'EmployeeDetails'[Employee ID] = EARLIER ( 'EmployeeDetails'[Employee ID] )
),
'EmployeeDetails'[Begin Date], DESC
)
)
RETURN
MAXX ( LatestRecord, 'EmployeeDetails'[Status] )
),
[@LatestStatus] = "Active"
)
)

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.

[PatLam0187]

Hello @v-lgarikapat ,

Thank you for the code but for some reason I'm getting an error:

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

Let me know if further modifications are needed.

Thank you

[v-lgarikapat]

Hi @PatLam0187 ,

Thanks for the follow-up question.

I’ve modified the DAX logic and uploaded the PBIX file here for reference.

Source data 

Result 

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.

[PatLam0187]

Hello @v-lgarikapat ,

Thank you for your consistent reply, appreciate it a lot.

The DAX code is still counting individual records that were active "as of date".

What I actually need is all records that are active. For example if I filter as of March 01, 2025 to March 15, 2025, the total records should be 3: 1002, 1003, and 1005 as the strt date of of 1004 being active is June 01, 2025.  And if I then filter as of June 01, 2025 to June 15, 2025, I should only see 4, 1002, 1003, and 1004 because 1005 is terminated as of May 15, 2025.

Thanks again

[v-lgarikapat]

Hi @PatLam0187 ,

Thank you for the detailed explanation.

Based on your screenshot and guidance, I have implemented the logic accordingly and uploaded the PBIX file for your review. Kindly take a moment to review it and let us know your feedback.

If your issue has been resolved, please consider marking the most helpful reply as the accepted solution. This helps other community members who might encounter a similar issue to find answers more efficiently.

If you're still facing any challenges, please feel free to reach out we'll be happy to assist you further.

Looking forward to your response.

Best regards,
LakshmiNarayana

[v-lgarikapat]

Hi @PatLam0187 ,

If your question has been answered, kindly mark the appropriate response as the Accepted Solution. This small step goes a long way in helping others with similar issues.

We appreciate your collaboration and support!

Best regards,
LakshmiNarayana

[v-lgarikapat]

Hi @PatLam0187 ,

If your question has been answered, kindly mark the appropriate response as the Accepted Solution. This small step goes a long way in helping others with similar issues.

We appreciate your collaboration and support!

Best regards,
LakshmiNarayana

[PatLam0187]

@v-lgarikapat , apologies for the delay I was away for a different project. The code did not work for me it still counted as of the date and the total of active profiles. I had modified my data source with the end date of each action. Please let me know if this is better and also note that I would also need a monthly progression of how many "actives" there are.

Again, thank you for your support.

New Source in next reply.

[PatLam0187]

Employee No.	Employment Status	Begin Date	End Date	Action Type	Reason for Action
2205037	Active	2024-09-03	2025-03-24	New Hire	New Position
2205037	Active	2025-03-25	2025-03-30	Leave of Absence	STD
2205037	Active	2025-03-31	2025-04-29	Return from Leave	Return From Leave
2205037	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205038	Active	2024-09-03	2024-12-28	New Hire	New Position
2205038	Withdrawn	2024-12-29	9999-12-31	Termination	Resignation
2205039	Active	2024-09-16	2025-04-29	New Hire	New Position
2205039	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205040	Active	2024-09-16	2025-02-21	New Hire	New Position
2205040	Withdrawn	2025-02-22	9999-12-31	Termination	Resignation
2205041	Active	2024-09-16	2025-01-22	New Hire	New Position
2205041	Withdrawn	2025-01-23	9999-12-31	Termination	Resignation
2205042	Active	2024-09-16	2025-04-29	New Hire	New Position
2205042	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205043	Active	2024-09-16	2025-04-29	New Hire	New Position
2205043	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205044	Active	2024-09-16	2025-02-17	New Hire	New Position
2205044	Withdrawn	2025-02-18	9999-12-31	Termination	Resignation
2205045	Active	2024-09-16	2025-04-29	New Hire	New Position
2205045	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205046	Active	2024-09-23	2025-02-10	New Hire	New Position
2205046	Withdrawn	2025-02-11	9999-12-31	Termination	Dismissal
2205047	Active	2024-09-23	2025-04-29	New Hire	New Position
2205047	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205048	Active	2024-09-23	2025-04-29	New Hire	New Position
2205048	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205049	Active	2024-09-23	2025-04-29	New Hire	New Position
2205049	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205050	Active	2024-09-23	2025-04-29	New Hire	New Position
2205050	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205051	Active	2024-09-23	2025-04-29	New Hire	New Position
2205051	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205052	Active	2024-09-23	2025-04-29	New Hire	New Position
2205052	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205053	Active	2024-09-16	2025-04-29	New Hire	New Position
2205053	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205054	Active	2024-09-30	2025-04-29	New Hire	New Position
2205054	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205055	Active	2024-10-14	2024-10-14	New Hire	New Position
2205055	Active	2024-10-15	2025-04-29	Change in Position	Lateral
2205055	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205056	Active	2024-10-14	2025-04-29	New Hire	New Position
2205056	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205057	Active	2024-11-11	2025-04-29	New Hire	New Position
2205057	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205058	Active	2024-10-21	2025-04-29	New Hire	New Position
2205058	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205059	Active	2024-10-21	2025-04-29	New Hire	New Position
2205059	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205060	Active	2024-10-21	2025-04-29	New Hire	New Position
2205060	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205061	Active	2024-10-21	2024-11-29	New Hire	New Position
2205061	Withdrawn	2024-11-30	9999-12-31	Termination	Resignation
2205062	Active	2024-10-21	2025-04-29	New Hire	New Position
2205062	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205063	Active	2024-10-21	2025-04-29	New Hire	New Position
2205063	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205064	Active	2024-11-04	2025-04-29	New Hire	New Position
2205064	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205065	Active	2024-11-04	2025-04-29	New Hire	New Position
2205065	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205066	Active	2024-11-11	2025-04-29	New Hire	New Position
2205066	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205067	Active	2024-11-11	2025-04-29	New Hire	New Position
2205067	Active	2025-04-30	2025-05-26	Change in Position	Lateral
2205067	Active	2025-05-27	9999-12-31	Change in Position	Perm. Bulletin/Award
2205068	Active	2024-11-11	2025-04-29	New Hire	New Position
2205068	Active	2025-04-30	9999-12-31	Change in Position	Lateral
2205069	Active	2024-11-05	2025-04-29	New Hire	New Position
2205069	Active	2025-04-30	9999-12-31	Change in Position	Lateral

[v-lgarikapat · Accepted solution]

Hi @PatLam0187 ,

Thank you for the detailed explanation.

Based on sample data  I have implemented the logic accordingly and uploaded the PBIX file for your review. Kindly take a moment to review it and let us know your feedback.

If you're still facing any challenges, please feel free to reach out we'll be happy to assist you further.

Looking forward to your response.

Best regards,
LakshmiNarayana

[PatLam0187]

@v-lgarikapat , This worked perfectly. Thank you!

[v-lgarikapat]

Hi @PatLam0187 ,

You're very welcome! I'm glad it worked out perfectly

Best Regards,

Lakshmi.

[v-lgarikapat]

Hi @PatLam0187 ,

I wanted to follow up and confirm whether you’ve had a chance to review the information we shared. If you have any questions or need further clarification, please don’t hesitate to reach out.

If you're still encountering any challenges, feel free to let us know we’d be glad to assist you further.

Looking forward to your response.

Best regards,
Lakshmi Narayana

[v-lgarikapat]

Hi @PatLam0187 ,

As we haven't heard back from you, we are closing this thread. If you are still experiencing the issue, please feel free to create a new thread we’ll be happy to assist you further.

Thank you for your patience and support.

If you found our response helpful, please mark it as Accepted Solution so others with similar queries can find it easily.

Best Regards,

Lakshmi Narayana

[v-lgarikapat]

Hi @PatLam0187 ,
Thank you for sharing the sample data. I’ll implement the logic based on this and share the sample PBIX file with you shortly.

Appreciate your patience and understanding.

Best Regards,

Lakshmi Narayana

[ryan_mayu]

@PatLam0187 

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