Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
VaughanM
New Member

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 Name, Start Date, End Date. 
I have a "Calendar" table with the following fields: Date, Month, Year, YearMonth.

 

Calendar is linked to Persons via an Active One to Many Relationship to Start Date. And a non-active relationship between date and End Date.

How I can calculate how many staff at present at each month and year using this information. 

As the result I'm getting at the moment are only returning data for the month where people have a start date. 

For Example Aiden Sally's Count should appear from Jun 2018 - March 2023.

For Example Yuliia Susan's Count should appear from Jun 2022 - March 2023.

 

image.png

8 REPLIES 8
johnt75
Super User
Super User

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

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.

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_0-1676993484179.png

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

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_0-1676995166878.png

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.  

VaughanM_2-1676995392857.png

 

VaughanM_1-1676995388313.png

 

You're missing the COUNTROWS from the CALCULATE statement

I've just re-added it and the numbers are still the same as the first statement. 

VaughanM_0-1676996139933.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors