Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Use relative date slicer to show 'active' projects

Hello, 

I'm new to PowerBI and struggling a bit. 

I have a data table of projects with start and end dates. Some projects have end dates in the future, as in Project 1 & 3:

Project Table:

NAMESTART DATEEND DATE
Project 11/1/202010/31/2027
Project 25/1/201611/30/2020
Project 33/10/20233/31/2024
Project 46/1/20187/25/2019

 

I want to have a relative date slicer that will allow users to search for all projects that were active in a specific date range (e.g. if I searched for the last 3 years in the relative date slicer (May 2020-May 2023), the table would only show Projects 1, 2 & 3. 

I've tried several different ways to achieve this: for e.g. I created a date table, and then created a new column in my project table with the code:

Active = IF(MAX('Date Table'[Date])>='Project Table'[Start Date]&&MIN('Date Table'[Date])<='Project Table'[End Date], "Active", "Inactive")
Added the 'Active' column to the projects table and then filtered for 'Active'.

 

But it keeps leaving out Project 2 because the start date is earlier than May 2020, even though the project was still active in the timeframe selected. How can I get the relative date slicer to recognize 'active' projects, even if they started before the earliest date in the relative date slicer?

Any help/suggestions would be appreciated. Thank you!

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , refer to the approach with the date table and measure, in the blog and attached file for similar data

 

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-trend/ba-p/882970

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Hi, Sorry for the late reply. I used the 'current employee' example and it sort of worked, but it's still leaving out some projects that should be considered 'active'. For example, if I use the relative date slicer to find projects that were active within the last 5 years (which would be going back to June 2018), it will find project A (start date: July 2020, end date: November 2024), but will leave out project B (Start date January 2016, end date June 2019). While project B started before the June 2018 cut off, it was still active within the last 5 years.

 

Is there a way to fix this? I do appreciate any help or advice.

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.