Forum Discussion

skasper's avatar
skasper
Responsive Resident
9 years ago
Solved

Active Employees per Period

Hi,   struggling with what should be simple. I have two tables ('crm_employees' and 'DimDates'). There exists a relationship between them (see picture below). What I am trying to calculate i...
Need Help
  • skasper's avatar
    skasper
    9 years ago

    Thank you for the advice. I tried it and do not get the correct result, either. What I do get is the number of employees, who left in a specific period (e.g. month).

     

    As I have to deliver the report today, I resolved to take a different approach, showing the number of joiners, leavers and overall evolution of active personnel as in the chart below.

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

skasper,

Please use the DAX below to calculate Active Employees and check if you get expected result.

Active Employees = 
CALCULATE(
	COUNTA('crm_employees'[Employee]);
	FILTER('crm_employees' , ('crm_employees'[Contract Start] <= LASTDATE('DimDates'[Date]) 
&& 'crm_employees'[Contract End]>= FIRSTDATE('DimDates'[Date])))

)



Regards,

skasper's avatar
skasper
Responsive Resident
9 years ago

Thank you for the advice. I tried it and do not get the correct result, either. What I do get is the number of employees, who left in a specific period (e.g. month).

 

As I have to deliver the report today, I resolved to take a different approach, showing the number of joiners, leavers and overall evolution of active personnel as in the chart below.

 

  • ILAN102010SH's avatar
    ILAN102010SH
    Frequent Visitor
    9 years ago

    I have the excat same probles.

    please help

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Sascha,

     

    I like your apporach. I am new to Power BI - and am looking to sort Joiners, Leavers and Active Running total by month, quarter and YTD. Our fiscal year is July-June. Appreciate your help here.

     

    Thanks.

    • rl_evans's avatar
      rl_evans
      Helper II
      7 years ago

      I encountered this same requirement this week.  Here is how I solved it.

      # Employees At End of Period =
      VAR MaxDate = MAX ( 'Date'[Date] )

      VAR EmpCnt =
          CALCULATE (
              COUNTROWS (
                  CALCULATETABLE ( 'Employees', 'Employees'[HireDate] <= MaxDate, ALL ( 'Date' ) )

                                      ),
                                    (ISBLANK ( 'Employees'[TerminationDate] ) || 'Employees'[TerminationDate] > MaxDate)
                             )
      RETURN
      IF ( ISBLANK ( EmpCnt ), 0, EmpCnt )

       

      In my data set, employees that are currently active have a blank termination date.  The Calculate function returns Blank, instead of zero, when the count is zero for any given period; which is why the last IF is used after the RETURN.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        rl_evans

         

        I see that you do take the acocunt for termintation dates in the future, correct ?

         

        Im asking cause it seem that DAX calculation is exactly excluding all of current active employees with a termination date in the future, and cant figure out why that is.

         

         

         

         

         

         

         

  • johan-svitla's avatar
    johan-svitla
    Regular Visitor
    3 years ago

    I have the same exact issue. Can anybody share how to calculate joiners, leavers, and active running total?

    It'd be highly appreciated

  • shreeomsingh's avatar
    shreeomsingh
    Frequent Visitor
    1 year ago

    Could you please share your sample power bi pbix file for above chart. it's urgent