As of Date or Current Date

[bmurphy]

Trying to get an As of Date or Current Date calendar search option to work on Active Employee or New Hires or Termed Employees type reports.  So for example,  whatever date is selected/chosen, ONLY those Active EE's on that selected date would appear.  If the date selected was 11/11/2024, only Employees who were active would be on the report....basing it off Latest Hire Date field.

Anyone with experience using this type of Date option?

Dates = calendar(min(EIS_EMPLOYEE_VW[Latest Hire Date]),max(EIS_EMPLOYEE_VW[TERMINATION_DATE]))

For Date Search box:   Date Search = Dates[Date]

Created an additional column under the Employee view:

Active EE Date Filter = VAR CurrentDate = SELECTEDVALUE('Dates'[Date])

VAR HireDate = SELECTEDVALUE('EIS_EMPLOYEE_VW'[Latest Hire Date])

VAR TermDate = SELECTEDVALUE('EIS_EMPLOYEE_VW'[TERMINATION_DATE])

RETURN

IF(HireDate <= CurrentDate && TermDate = BLANK() || TermDate >= CurrentDate , "Filter In" , "Filter Out")

It's just not pulling in right.  It's including Employees with hire dates AFTER the date in the calendar.

Any help is appreciated.

[danextian · Accepted solution]

Hi @bmurphy 

Try this and ensure that the dates table is not related to your employees table

Active Employees = 
VAR _SelectedDate = 
    -- Get the latest selected date from the Dates table
    MAX ( Dates[Date] ) 

RETURN
    CALCULATE (
        -- Count the number of employees
        COUNTROWS ( 'Employee' ),
        
        -- Filter for employees who have been hired on or before the selected date
        'Employee'[HireDate] <= _SelectedDate,
        
        -- Include employees who either:
        OR (
            -- Have no termination date (still active)
            ISBLANK ( 'Employee'[TerminationDate] ),
            
            -- Or, have a termination date in the future (still employed)
            'Employee'[TerminationDate] > _SelectedDate
        )
    )

Sample pbix attached.

[bmurphy]

I think this actually is working!  OMG.  Thank you!  Power BI is all new to me and so different than our reporting system I'm leaving, so I have so much to learn!   I'm sure many questions ahead!  I do have a question tho....how come you don't need to join it to the Employee table?  Because of what's included in the measure itself?

[danextian]

If you do, your data gets filtered to either the hire or termination date depending  on  which column the relationshiop is on.

[ryan_mayu]

Do you mean active on the selected day or active now? could you pls provide some sample data and the expected output?

[bmurphy]

Thank you for responding/asking.  It's active on the selected day.  If I put in 10/31/24, I want to know who was active on that day.  If I put in 07/12/2021, I want to see who was active on THAT day.

[bmurphy]

I really appreciate this and I'll take a look!

[v-karpurapud]

Hi @bmurphy 

Have you verified the files shared by @amitchandak and @danextian ? Could you please let me know if they resolve your issue? If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will assist other members with similar queries.

Thank You!

[bmurphy]

I apologize for the delay.  We are a LARGE company and I needed to do some validations amongst many other day to day tasks. 🙂

[amitchandak]

@bmurphy , Please refer to the attached files. You can also check

Power BI: HR Analytics - Employees as on Date: https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

[bmurphy]

Thank you so much for an actual example I can try and work off of.  I appreciate it.  Will do some validations and see if this will work!  Thank you!