Forum Discussion

VaughanM's avatar
VaughanM
New Member
3 years ago

Calculating Total Employees each Month

Hi All,   Looking to create a measure which tells me which employees are present at a certain month and year.    I have a "Persons" Table with the following fields: Full Name, First Name, Last Na...
calendar
DAX
employee
Staff
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

try

Active Employees =
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Staff' ),
        'Staff'[Start date] <= MaxDate
            && (
                'Staff'[End date] >= MinDate
                    || ISBLANK ( 'Staff'[End date] )
            )
    )
RETURN
    Result
VaughanM's avatar
VaughanM
New Member
3 years ago

Hi Johnt75,

 

The dax you provided only produces figures for when the employee starts. 

For Example Aiden Sally would only appear in June 2018 and not in future months.

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

    Try

    Active Employees =
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Staff' ),
        REMOVEFILTERS ( 'Staff'[Start date], 'Staff'[End date] ),
        'Staff'[Start date] <= MaxDate
            && (
                'Staff'[End date] >= MinDate
                    || ISBLANK ( 'Staff'[End date] )
            )
    )
RETURN
    Result
    • VaughanM's avatar
      VaughanM
      New Member
      3 years ago

      I'm getting this error now appearing with this updated one. 

      "the expression refers to multiple columns. Multiple columns cannot be converted to scalar value."

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

        can you post a shot of the full measure so we can see where the red lines are and see what exactly it is complaining about