Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

[Error] Calculate Employee Count per Month

Dear all,

 

I want to determine the number of active employee per month, so I created the following DAX :

Current Employees =
CALCULATE
    (COUNTx
        (FILTER('employee list','employee list'[Join Date]<=max('calendar'[Date]) &&
        (ISBLANK('employee list'[Leaving date]) ||
        'employee list'[Leaving date]>max('Calendar'[Date]))),'employee list'[Personnel No.]),
            CROSSFILTER('employee list'[Join Date],'Calendar'[Date],None))
 
And the result is opposite to my expectation, because it seems it counts employee from previous month


Any idea what's wrong with my DAX?
 
 
Thank you and appreciate your help.
  • amitchandak's avatar
    amitchandak
    3 years ago

    Anonymous , Active employees are calculated from the beginning and you have active employees since 1951. So seem correct to me, Small change needed

    Current Employees = 
CALCULATE(COUNTx(FILTER('employee list','employee list'[Join Date]<=max('calendar'[Date]) && not(ISBLANK('employee list'[Join Date])) &&
(ISBLANK('employee list'[Leaving date]) || 
'employee list'[Leaving date]>max('Calendar'[Date]))),'employee list'[Personnel No.]),
CROSSFILTER('employee list'[Join Date],'Calendar'[Date],None))


Hired Employee = 
CALCULATE
    (COUNT('employee list'[Personnel No.]),
    USERELATIONSHIP('employee list'[Join Date],'Calendar'[Date] ), not(ISBLANK('employee list'[Join Date])))

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , Active employees are calculated from the beginning and you have active employees since 1951. So seem correct to me, Small change needed

    Current Employees = 
CALCULATE(COUNTx(FILTER('employee list','employee list'[Join Date]<=max('calendar'[Date]) && not(ISBLANK('employee list'[Join Date])) &&
(ISBLANK('employee list'[Leaving date]) || 
'employee list'[Leaving date]>max('Calendar'[Date]))),'employee list'[Personnel No.]),
CROSSFILTER('employee list'[Join Date],'Calendar'[Date],None))


Hired Employee = 
CALCULATE
    (COUNT('employee list'[Personnel No.]),
    USERELATIONSHIP('employee list'[Join Date],'Calendar'[Date] ), not(ISBLANK('employee list'[Join Date])))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello amitchandak thanks for the help, it works well