Forum Discussion
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.
8 Replies
johnt75Super 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 ResultHi 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
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
