Filtering Active Employees Within a Selected Date Range in Power BI (Start and End Dates)

[rajkumarg1991]

Dear All,

I'm building a Power BI dashboard where I need to display only those employees who were active at any point during a user-selected date range.

An employee is considered active within the range if:

• Start Date ≤ End Date, AND

• End Date (or blank, meaning still active) ≥ Start Date

Example:
If a user selects 01-Jan-2024 to 31-Jan-2024, I want to include employees who worked on any date within that range, not necessarily the entire period.

My Power BI model includes the following key tables and relationships:

1. appointment Table

This is the core employment fact table. It contains:

• Person Number (foreign key to the person table)

• Start Date and End Date for each appointment

Multiple rows per person (in case of multiple contracts/assignments)

2. person Table

This is the dimension table for demographic attributes, including:

• Gender, Disability, Ethnicity, LGBTQIA+ status, etc.

• One row per individual

• Connected to appointment via a one-to-many relationship:

person[Person Number] → appointment[Person Number]

This relationship allows me to group or slice active employees by demographic fields using data from the person table.

3. DateTable

A standalone calendar table created using:

DateTable = CALENDAR(MIN(appointment[Start Date]), MAX(appointment[End Date]))

This is an unrelated (disconnected) date table

It’s used as a slicer to let the user select a date or date range

🚫This table is intentionally left disconnected from the appointment table.
This is because:

• We cannot create an active relationship to both Start Date and End Date simultaneously.

• A single relationship (to either Start Date or End Date) would not accurately capture overlaps, and cause incomplete or misleading filtering.

• Filtering is handled using DAX measures instead of model relationships is not working.

Any advice or proven approaches on how to logically and efficiently implement this type of date range overlap filter in Power BI would be greatly appreciated.

[Ashish_Excel · Accepted solution]

Hi,

I have solved a similar question here.

Hope this helps.

[v-ssriganesh · Accepted solution]

Hi @rajkumarg1991,
Thank you for reaching out to the Microsoft fabric community forum.

I have reproduced your scenario in Power BI using sample data and a disconnected DateTable, as described. I implemented a solution using DAX logic to filter and show only those employees who were active during the selected date range, including cases with blank end dates.

What I Did:

• Used a disconnected DateTable as a slicer.
• Built a DAX measure to identify overlapping employment periods with the selected range.
• Applied the logic even when multiple appointments per person existed.
  
  

Output:

The measure correctly returns employees who were active at any point within the selected date range, matching your expectations.

I’m attaching the .pbix file here for your reference so you can explore and adapt it to your dataset and visual requirements.

Thank you, @Ashish_Excel & @sevenhills for share your valuable insights.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh · Accepted solution]

Hi @rajkumarg1991,
Thank you for reaching out to the Microsoft fabric community forum.

I have reproduced your scenario in Power BI using sample data and a disconnected DateTable, as described. I implemented a solution using DAX logic to filter and show only those employees who were active during the selected date range, including cases with blank end dates.

What I Did:

• Used a disconnected DateTable as a slicer.
• Built a DAX measure to identify overlapping employment periods with the selected range.
• Applied the logic even when multiple appointments per person existed.
  
  

Output:

The measure correctly returns employees who were active at any point within the selected date range, matching your expectations.

I’m attaching the .pbix file here for your reference so you can explore and adapt it to your dataset and visual requirements.

Thank you, @Ashish_Excel & @sevenhills for share your valuable insights.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[rajkumarg1991]

Hi @v-ssriganesh,

Thank you so much for reproducing the scenario and sharing the .pbix file, the solution works perfectly when displaying data in a table format. The use of a disconnected DateTable along with DAX logic for overlapping employment periods is very clear and helpful.

I do have a follow-up question:
Currently, I'm not displaying the data in a table visual, I’m using cards and other visuals on the dashboard. How can I apply the same active date range filtering logic to those visuals (e.g., cards showing total count, bar charts by gender or disability)?

Also, I’m curious,
Is there any way to implement this using dynamic parameters (like Dynamic M Query Parameters or field parameters) for DirectQuery-based filtering, or is DAX-based virtual filtering considered the best practice for this type of logic?

Thanks again for your support!

[v-ssriganesh · Accepted solution]

Hello @rajkumarg1991,

Thank you for your kind feedback I’m glad the solution worked well in your scenario.

Absolutely the same DAX filtering logic can be extended to cards, bar charts, and other visuals You can reuse the existing measure (or its logic) inside any visual that aggregates data.

Use the measure as a visual-level filter (e.g: Is Active in Range = 1) or include its logic inside new measures tailored for each visual.

If you're using DirectQuery, especially against large datasets, you can consider dynamic M Query Parameters & Field Parameters.

For this kind of interval-based filtering, where the logic involves comparing start/end dates against a range Using DAX-based virtual filtering (like in your current approach) is still considered the most practical and flexible method, especially when using Import mode or Hybrid tables.

If your query has been answered, please “Accept as Solution” and Give “Kudos” so others with similar issues can easily find the resolution.
Thank you.

[Ashish_Excel · Accepted solution]

Hi,

I have solved a similar question here.

Hope this helps.

[sevenhills]

Your approach seems to be correct. Disconnected calendar table is the right approach too. 

Check this link https://www.daxpatterns.com/events-in-progress/

Not clear whether you are also looking for dashboard samples: In case, you are looking, check this link for community data stories gallery https://community.fabric.microsoft.com/t5/Data-Stories-Gallery/bd-p/DataStoriesGallery 

You can try filtering for Employee, HR, Hospital and use it for your needs. If you are looking for specific thing, let the group know ... 

Hope it helps!