Headcount DAX Formula

[Anonymous]

Hello,

I am new to Power BI and need your precious help here.

My data is as follows:

My calendar is as follows:

What is the formula for headcount per month please?

I am trying this but it is not functioning..

Headcount = CALCULATE(DISTINCTCOUNT(Employees[EmployeeID]),FILTER(Employees,and(Employees[DateStart]<=[Selected date],OR(Employees[DateDeparture]=BLANK(),Employees[DateDeparture]>[Selected date]))))

Thank you so much for your assistance

Ariane

[Anonymous]

IT'S GOOD! 

Thank you so much @johnt75 ! You rock! 😃💪

[johnt75]

Try

Headcount =
VAR SelectedDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Employees[EmployeeID] ),
        Employees[DateStart] <= SelectedDate
            && (
                ISBLANK ( Employees[DateDeparture] )
                    || Employees[DateDeparture] > SelectedDate
            )
    )

[Anonymous]

Hello,

I am having this error

Cannot find the table 'Date'. 

Thank you for your help

[johnt75]

Change 'Date'[Date] to 'Calendar'[Date]

[Anonymous]

It is still not good 😕

From my excel I have 182 employees in Dec 2022 but the headcount showing 229.

It is still taking employees who left in 2021

[Anonymous]

Looks like I found the mistake.

The departure date should have been put in all the months of the employees who left. I had included it only in the month they left.