Use relative date slicer to show 'active' projects

[Anonymous]

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:

NAME	START DATE	END DATE
Project 1	1/1/2020	10/31/2027
Project 2	5/1/2016	11/30/2020
Project 3	3/10/2023	3/31/2024
Project 4	6/1/2018	7/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!

[amitchandak]

@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-tr...

[Anonymous]

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.