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

6 Replies

  • 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'S GOOD! 

     

    Thank you so much johnt75 ! You rock! ðŸ˜ƒðŸ’ª