Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.