Cumulative Distinct Employee Count with Multiple Filters

[Anonymous]

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)

I need the user to be able to reconcile the rolling count vs the monthly hiring and termination data.   

Any help would be greatly appreciated and I am happy to use something else. 

Thanks

[lbendlin]

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-...
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/1447523

[Anonymous]

Active 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

[lbendlin · Accepted solution]

Here is one possible implementation.  It uses a disconnected calendar table.