I'm trying to return the count of staff that are employed on any given day/month/year. Table1 contains the staffID, StartDate and TerminatedDAte. Table2 is a date dimension. The current count is returning basically no results for all dates in 2018. Where have i gone wrong?
I want to be able to return '### staff were employed on 22/01/2018' or month on month active employee counts.
Count of Active Employees =
VAR currentdate =
[Staff ID Count],
(Table1[StaffStartDate] <= currentDate
&& Table1[StaffTerminationDate] >= currentDate )
Logic may need rework. What if someone's termination date was about a week ago and the currentdate is today. He will be counted as an active employee because you are filtering the table to allow any entries below the current date.
Thanks Ahrne, i think you're onto something. I have not handled the blanks in the terminated date column.