Forum Discussion
Cumulative Distinct Employee Count with Multiple Filters
Hi,
I have an employee snapshot taken monthly and I need to do get a distninct active employee count every month.
I have seen several examples and still cannot get it to work correctly.
My problem is that the employees hired before my graph range (based upon a rolling 13 months) are not included
My last example measure is
| Test Employee Count = |
| VAR MaxDate= MAX('Filter Dates'[Date]) |
| VAR MinDate=min('Filter Dates'[Date]) |
| RETURN |
| Calculate(DISTINCTCOUNT(vwMSRHireAttrition_UATT[UniqueId]), |
| OR(vwMSRHireAttrition_UATT[Adjusted Hire Date]<=MinDate, ISBLANK(vwMSRHireAttrition_UATT[Adjusted Hire Date])), |
| vwMSRHireAttrition_UAT[Termination Date]>=MaxDate) |
Here is one possible implementation. It uses a disconnected calendar table.
3 Replies
What does "active" mean in your context? Termination date is null or in the future?
Please provide sanitized sample data that fully covers your issue. https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Active would employees that are not terminated and hired before a month. The key issue is that the graph is supposed to be rolling twelve months but I have many employees hired years ago. Thanks
Employee Contract Hire Date Termination Date Cost Center Unique ID Bob A 4/1/2020 Test Bob-A Tom B 7/1/2021 6/1/2022 One Tom-B John C 12/1/2021 JW John-C Mary A 5/1/2016 HP Mary -A Steve C 1/1/2022 4/1/2022 SDS Steve-C Here is one possible implementation. It uses a disconnected calendar table.