The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
User | Count |
---|---|
78 | |
74 | |
43 | |
32 | |
28 |
User | Count |
---|---|
104 | |
93 | |
52 | |
50 | |
46 |