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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

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

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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