[Error] Calculate Employee Count per Month

[Anonymous]

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?

Here is the PIBX File https://drive.google.com/file/d/19dY4admNRFwl5n5YVhpp2SJpuCuptGTw/view?usp=share_link

Thank you and appreciate your help.

[amitchandak · Accepted solution]

@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]

Hello @amitchandak thanks for the help, it works well