I need to know the proper DAX to accommodate a couple of data filters on an allocation table.
I need to get headcount that can be filtered by Month where the employee has a start date before on the 1st day of the filtered month AND where their end date is either blank or less than the last day of that filtered month.
An example would be I filter on March:
I want all employees that have a start date <= 3/31/2019 and an end date that is blank or >= 3/1/2019
How do I write a general DAX formulat that takes into count what the filtered month is. I don't want to hard code dates into anything.
Head Count =
CALCULATE (
DISTINCTCOUNT ( Table[Employee ID] ),
where
Table[Start Date] <= Filtered Month (last day of that month)
AND
(Table[End Date] ISBLANK OR Table[End Date]>= Filtered Month( the first day of that filtered month)
