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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Vishal7012
Regular Visitor

Resignation count

Hi,

 

I'm trying to get the count of employees who are serving notice period as of today from the database. Primarily, if the last working date of an employee is beyond today, it should count as the employee serving notice.

 

For eg. from the below table, count of employees serving notice period should be 3 (10003,10004,10005) as the last working date is beyond today.

Employee IDLast working date
1000112-Dec-22

10002

1-Jan-23
1000331-Jan-23
100041-Feb-23
1000521-Feb-23
100061-Nov-22

 

1 ACCEPTED SOLUTION
eliasayy
Impactful Individual
Impactful Individual

oh my bad 

eliasayy_0-1674315768831.png

Count =
VAR _table = SUMMARIZE(FILTER('Table','Table'[Last Working date] <>BLANK()),'Table'[Employee Id],"Terminated",[Terminated])
Return
COUNTROWS(FILTER(_table,[Terminated] = "Yes"))

View solution in original post

4 REPLIES 4
eliasayy
Impactful Individual
Impactful Individual

For visual purpose.

Simply add a new measure 

Today's date = TODAY()

 

now add thisbto yoir visual,

 

Create another measure 

 

Terminated = if(max(table[last working day]) < [Today's Date], "yes","no")

 

Now create a count measure

 

Count measure = 

Var summary = summarize(table,[employee id],"terminated column",[Termintaed]

Return

Calculate(distinctcount([terminated column]),terminated = ""yes")

Thanks. There is a syntax error i'm getting while i use the count DAX wherein i'm unable to get the "terminated". Just doesnt seem to identify

Calculate(distinctcount([terminated column]),terminated = "yes") 

eliasayy
Impactful Individual
Impactful Individual

oh my bad 

eliasayy_0-1674315768831.png

Count =
VAR _table = SUMMARIZE(FILTER('Table','Table'[Last Working date] <>BLANK()),'Table'[Employee Id],"Terminated",[Terminated])
Return
COUNTROWS(FILTER(_table,[Terminated] = "Yes"))

thanks, this works!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors