cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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 ID Last working date 10001 12-Dec-22 10002 1-Jan-23 10003 31-Jan-23 10004 1-Feb-23 10005 21-Feb-23 10006 1-Nov-22

1 ACCEPTED SOLUTION
Impactful Individual

Count =
VAR _table = SUMMARIZE(FILTER('Table','Table'[Last Working date] <>BLANK()),'Table'[Employee Id],"Terminated",[Terminated])
Return
COUNTROWS(FILTER(_table,[Terminated] = "Yes"))
4 REPLIES 4
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")

Regular Visitor

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")

Impactful Individual

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

thanks, this works!