Forum Discussion

tebtim19's avatar
tebtim19
Icon for Helper IV rankHelper IV
5 years ago
Solved

Count If for active employees

I have a table for all employees with the ff tables:

 

Datehired

Dateterminated

Job Status

 

If the employee is permanent the format we use in dateterminated was 5555.01.01

 

Anyone who can help me to get the number of

Active employee

Terminated employee

Number of hired employee

 

Thanks. 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi,  tebtim19 

    Try measure as below:

    Active employee = CALCULATE(COUNT('Table'[Employee id]),FILTER('Table','Table'[Entry Date]<=TODAY()&&'Table'[Leave Date]>=TODAY()))
    Terminated employee = CALCULATE(COUNT('Table'[Employee id]),FILTER('Table','Table'[Leave Date]<=TODAY()))
    Number of hired employee = CALCULATE(COUNT('Table'[Employee id]),FILTER('Table','Table'[Entry Date]<=TODAY()))

     

    Best Regards,
    Community Support Team _ Eason

8 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    "If the employee is permanent the format we use in dateterminated was 5555.01.01"

    That is a very unfortunate choice. Imagine the thousands of rows in your calendar table that need to be generated to cover the date range from the first hire date to that date.  It is better to use today's date as the cutoff for the calendar table, and use the absence of a termination date for the calculations.

     

    Please add a Dates/Calendar table to your data model. Please share sample data and show the expected outcome.

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi,  tebtim19 

    Try measure as below:

    Active employee = CALCULATE(COUNT('Table'[Employee id]),FILTER('Table','Table'[Entry Date]<=TODAY()&&'Table'[Leave Date]>=TODAY()))
    Terminated employee = CALCULATE(COUNT('Table'[Employee id]),FILTER('Table','Table'[Leave Date]<=TODAY()))
    Number of hired employee = CALCULATE(COUNT('Table'[Employee id]),FILTER('Table','Table'[Entry Date]<=TODAY()))

     

    Best Regards,
    Community Support Team _ Eason

    • tebtim19's avatar
      tebtim19
      Icon for Helper IV rankHelper IV
      5 years ago

      Hi thanks,

      It looks working but the problem is the date. Looks like not working properly.

       

      First thing I did is replace the date from 5555,12,31 to 2021,12,31 

       

      Then I use this in my table:

      Dates =
      ADDCOLUMNS (
      CALENDAR ( DATE ( 2012, 1, 1 ), DATE ( 2021, 12, 31 ) ),
      "Year", YEAR ( [Date] ),
      "Month", FORMAT ( [Date], "MMMM" ),
      "Month #", MONTH ( [Date] ),
      "Year Month", YEAR ( [Date] ) & " " & FORMAT ( [Date], "MMMM" ),
      "Year Month #", YEAR ( [Date] ) * 100 + MONTH ( [Date] )
      )
       
      Then I add relationship to my date to entrydate and leavedate and use the date for my slicer.
      But still getting the wrong number of Active Employee. 
      when I filtered year to 2020 and etc

       

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, tebtim19 

    I don't quite understand what you mean. If you expect to filter the date into a date period (year 2020), how would you distinguish between Active employee and hired employee?

     

    So it is recommended to filter the date to a specific point  time, and you just need to create a new measure to replace 'today'

    slicerdate = MAX(Dates[Date])

     

    Best Regards,
    Community Support Team _ Eason

     

    • tebtim19's avatar
      tebtim19
      Icon for Helper IV rankHelper IV
      5 years ago

      Hi this is much appreciated,

       

      but I also wanted to get 

       how many joiner every 3 months  

      that will show in my card maybe you have idea

      ?

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi , tebtim19 

        If you want to filter the date in a period time , try measure as below:

        Max_slicerdate = MAX(Dates[Date])
        Min_slicerdate = MiN(Dates[Date])
        Number of Joiner in this period = CALCULATE(COUNT('Table'[Employee id]),FILTER('Table','Table'[Entry Date]>=[Min_slicerdate]&&'Table'[Entry Date]<=[Max_slicerdate]))

        Then using slicer (Page2) or filte rpane(Page3) to filter the data.

         

        Please  check my sample pbix file.

         

        Best Regards,
        Community Support Team _ Eason
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.