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

      • VaughanM's avatar
        VaughanM
        New Member
        3 years ago

        Please see attached full shot of the measure. 

        I basically would like to get the figures below. So during December 2022 I had 179 Employees with a start date equal or less than 31/12/2022, and a leaving date equal or less than 31/12/2022 or blank. Then in Jan 2023 184. Etc etc.