Forum Discussion
Calculate Active Employees with only 1 Date Column
Hi Anonymous ,
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
Community SupportHi Anonymous ,
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.
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 Anonymous ,
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.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 Anonymous ,
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