Forum Discussion
Filtering Active Employees Within a Selected Date Range in Power BI (Start and End Dates)
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.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.
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.
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!
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.