Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Headcount DAX Formula

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 thi...
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

Try

Headcount =
VAR SelectedDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Employees[EmployeeID] ),
        Employees[DateStart] <= SelectedDate
            && (
                ISBLANK ( Employees[DateDeparture] )
                    || Employees[DateDeparture] > SelectedDate
            )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello,

    I am having this error

    Cannot find the table 'Date'. 

    Thank you for your help

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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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