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
SCTomas92
New Member

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

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:

  1. 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])))
     
  2. 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.

1 REPLY 1
johnt75
Super User
Super User

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

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.