I have a column of dates. If an employee has left the department, there is an end date that is in the past. If the employee is still in that department, the end date is listed as 12/31/9999 because people here just don't want to leave blanks. (Personal pet peeve). I need to count the number of employee in that department who are current. In other words, I would like to do a DISTINCTCOUNT[EmployeeCode]) where the date is equal to 12/31/9999. My goal is to add this in query editor or as a measure. I have tried: CALCULATE(DISTINCTCOUNT([EmployeeCode]), [End Date]="12/31/9999")
Being as how I only want the number it is failing due to not using the end date within the matrix. I need the data to appear as:
Dept1 3
Dept2 3
Dept3 2
Dept4 4
I am not even a beginner in M
Proud to be a Super User!
Solved! Go to Solution.
Nevermind. I did this:
CALCULATE(COUNTROWS('Pod members'), FILTER('Pod members', [End Date]>TODAY()))
Proud to be a Super User!
Nevermind. I did this:
CALCULATE(COUNTROWS('Pod members'), FILTER('Pod members', [End Date]>TODAY()))
Proud to be a Super User!
User | Count |
---|---|
119 | |
62 | |
56 | |
47 | |
39 |
User | Count |
---|---|
110 | |
65 | |
61 | |
53 | |
47 |