Number of users active in a selected date (with start date and end date)

SCTomas92 — Mon, 29 May 2023 13:03:15 GMT

Hi all,

I require assistance with some Power BI measures. I'm trying to determine the number of active employees within a selected timeframe based on the "Date" (contract start date) and "EndDate" (contract end date) fields. Blank values in the "Date" field indicate that the employee was already active when data collection began, while empty values in the "EndDate" field signify that the employee is presently active. Whenever an employee gets promoted, their record is updated with an "EndDate" value, and a new entry is created.

The objective is to allow the end user to select two separate periods using slicers and view a detailed table showing which employees are new or no longer employed by the company. To facilitate this, I have duplicated the calendar table.

The measures I have created are as follows:

Employees_FirstMonthSelected: This measure calculates the count of active employees in the first selected month.
Employees_FirstMonthSelected =
CALCULATE( COUNTROWS(WorkDayGlobal) + 0,
AND(
OR(ISBLANK(WorkDayGlobal[Date]), WorkDayGlobal[Date] <= MAX(Calendar[Date])),
OR(ISBLANK(WorkDayGlobal[EndDate]), WorkDayGlobal[EndDate] > MAX(Calendar[Date])))
Employees_SecondMonthSelected: This measure calculates the count of active employees in the second selected month.
Employees_SecondMonthSelected =
VAR Max_Date =
MAX('Calendar2'[Date])
RETURN
CALCULATE( COUNTROWS(WorkDayGlobal) + 0,
WorkDayGlobal[Date] <= Max_Date || ISBLANK(WorkDayGlobal[Date]),
WorkDayGlobal[EndDate] > Max_Date || ISBLANK(WorkDayGlobal[EndDate]))

The issue I'm facing is that when I generate a detailed table of employees, some of them do not appear as active in periods when they were actually active (verified in the WorkdayGlobal table). For instance, if the user selects January 2023 and May 2023, an employee who worked from September 13, 2022, to April 11, 2023, and had a contract change with a start date of April 11 without an end date, should be considered active in both January 2023 and May 2023. However, they only appear as active in January.

I would greatly appreciate any insights on why this might be happening.

Thank you for your assistance.

Re: Number of users active in a selected date (with start date and end date)

johnt75 — Tue, 30 May 2023 11:01:14 GMT

Do you have any relationships between either of the calendar tables and the WorkDayGlobal table ?

topic Re: Number of users active in a selected date (with start date and end date) in DAX Commands and Tips

Number of users active in a selected date (with start date and end date)

Re: Number of users active in a selected date (with start date and end date)