Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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:
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!
@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
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
76 | |
57 | |
36 | |
34 |
User | Count |
---|---|
99 | |
56 | |
56 | |
46 | |
40 |