Filter by all projects currently active on a specific date

[KChyshMelly11]

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.

 

[v-juanli-msft · Accepted solution]

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,

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.

[KChyshMelly11]

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

[v-juanli-msft]

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

[KChyshMelly11]

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]

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.

[slounsbury]

Hi @KChyshMelly11 

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

Scott