cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kcantor
Community Champion
Community Champion

Calculation question - IF statement in Query Editor for Future Date

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION
kcantor
Community Champion
Community Champion

Nevermind. I did this:

CALCULATE(COUNTROWS('Pod members'), FILTER('Pod members', [End Date]>TODAY()))





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
kcantor
Community Champion
Community Champion

Nevermind. I did this:

CALCULATE(COUNTROWS('Pod members'), FILTER('Pod members', [End Date]>TODAY()))





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors