Forum Discussion
Filter by all projects currently active on a specific date
Hello,
I am trying to filter to show all projects currently active on a specific date.
In our data, we have a start date and an end date for the project and we would like to filter on a specific date and see what projects are active during that date.
Eg. The today line in the attached picture.
1.Create a date table, don't connect thsi new date table to any other table,
date1 = CALENDARAUTO()
Add "date" from this table to a slicer.
2. create measures in your table
selected date = SELECTEDVALUE(date1[Date]) active_specific_date = IF([selected date]<=MAX(Sheet12[end])&&[selected date]>=MAX(Sheet12[start]),1,0)
Add [active_specific_date] in the visual level filter of the Gantt chart,
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
v-juanli-msftCommunity Support
1.Create a date table, don't connect thsi new date table to any other table,
date1 = CALENDARAUTO()
Add "date" from this table to a slicer.
2. create measures in your table
selected date = SELECTEDVALUE(date1[Date]) active_specific_date = IF([selected date]<=MAX(Sheet12[end])&&[selected date]>=MAX(Sheet12[start]),1,0)
Add [active_specific_date] in the visual level filter of the Gantt chart,
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.This worked great. Thanks so much, Maggie! v-juanli-msft
slounsburyHelper II
- v-juanli-msft
It should be possible in Power BI with a date slicer from a connected calendar date table and measures created in your main data table.
Please show an example of your example data, eg,i table i supposed, does it structure correctly as yours?
project start end 1 1/1/2018 12/31/2018 2 3/1/2018 8/9/2018 3 11/1/2018 3/1/2019 4 4/1/2019 1/1/2020 Do you want the filtered result to show on the visual you post (just show the active projects on this visual)
or show on another visual like a table visual?
Best Regards
Maggie
Hi Maggie ( v-juanli-msft ),
Yes, the data for the dates that you provided is exactly how our data is structured. So we can link the start date, and we can link the end date, but we can't pick a certain date between the start and end dates.
The image I provided is just an example of one of the visuals that would use the data. The issue we are having is that we have no date and time linkages to span the date range, so we can use it for the filtering.
- v-juanli-msft
Is this problem sloved?
If not, please let me know.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
