cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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.

 Question.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Hi @KChyshMelly11 

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,

15.png

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.

View solution in original post

6 REPLIES 6
Highlighted
Helper II
Helper II

Hi @KChyshMelly11 

 

Can you add a date slicer on your report, selecting the specific date?

 

Scott

Highlighted
Community Support
Community Support

Hi @KChyshMelly11 

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

 

 

Highlighted

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. 

 

 

Highlighted
Community Support
Community Support

Hi @KChyshMelly11 

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,

15.png

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.

View solution in original post

Highlighted

Hi @KChyshMelly11 

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.

Highlighted

This worked great. Thanks so much, Maggie! @v-juanli-msft 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors