Filtering a Table by Selecting a Date and Returning Rows where the Date is between Two Dates

[ThePBIMaster]

Hi All,

I have a table with Employee Number ID, their start date, leave date and IDs for their division, location, and department.

If an employee starts on 01/01/2022 and moves to a different division on 31/03/2022, then their leave date for that role is the 31/03/2022 and they start their new role on 01/04/2022. This will add an extra row on the table with the same Employee Number ID (e.g. 86 below). Please note example below where employee number ID starts on the 04/11/2019 (2nd row of 86), leaves their first role on 08/03/2020 and starts their new role in Division 4 on 09/03/2020 (1st row of 86). 

I want an end user to be able to select a date in a slicer to filter this table so that only rows of data where the selected date is on or after the Start Date and on or before the Leave Date. This allows the end user to see how many employees where in each division/location/department at any point in time.

I'm trying to do something similar to this user - 

https://community.powerbi.com/t5/Desktop/Select-one-date-to-filter-data-between-two-dates/td-p/12272...

but instead of showing one value, I need to show three table visuals (one each for division, location, and department) with a sum of employees in each category.

Thanks in advance!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file whether it suits your requirement.

Employee count measure: =
CALCULATE (
    COUNTROWS ( DISTINCT ( Employee[Employee Number ID] ) ),
    FILTER (
        Employee,
        Employee[Start Date] <= MAX ( 'Calendar'[Date] )
            && OR (
                Employee[Leave Date] >= MIN ( 'Calendar'[Date] ),
                Employee[Leave Date] = BLANK ()
            )
    )
)

[ThePBIMaster]

@Jihwan_Kim 

Thank you! Working well 🙂