Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DAX: Distinct counts based on data parameters

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)

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    Please search this forum. There are many examples of such a calculation here.

    Best
    Darek