Filter active projects based on selected date range

[Anonymous]

Hey all,

I am working with data that looks very much like the following - 

Team	Project	Start	Finish
Team A	1	10/10/2021	3/2/2022
Team A	2	1/1/2022	2/1/2022
Team B	3	8/4/2021	5/1/2022
Team B	4	2/2/2022	2/20/2022
Team B	5	1/5/2022	4/10/2022
Team C	6	3/8/2022	4/10/2022

My goal is to filter all active Projects in a Gantt/Table for specific Months or custom Date Range, as well as selected Team.

Based on many recommendations on the forum,  I have tried creating a Date dimension table and using min/max to filter projects out, but I am not sure how to create a relationship with the main table so that I can filter Team. If I connect Date > Finish, then the Month filter only works for projects ending on that specific month, and I cannot have multiple relationships for Start. Was trying to use USERELATIONSHIP but was unable to make it work. 

Another possible solution that came to mind is to have a calculated column that has a filtering value based on the selected date value, but not sure how to make it work for a range.

I will also use the result to get a weighted average based on how long the project was active per month per team.

Any advice is appreciated!!

[bcdobbs]

So you have two main options.

First one is to leave your date table disconnected and do all the work in DAX.

Add a measure like:

Project Filter = 
VAR EarliestVisibleDate = MIN ( 'Date'[Date] )
VAR LatestVisibleDate = MAX ( 'Date'[Date] )

VAR FilteredProjects = 
    FILTER (
        Project,
        Project[Start] >= EarliestVisibleDate && Project[Finish] <= LatestVisibleDate
    )

RETURN INT ( NOT ( ISEMPTY ( FilteredProjects ) ) )

And use it as a visual filter set to 1:

Example file here

My prefered option is to change the grain of your table so there is one row per day. I'll send an example shortly.

[Anonymous]

The first option doesn't seem to work. I believe it does not apply to tasks that have their start/finish outside of the date range, which are also active projects.

[bcdobbs · Accepted solution]

Sorry I had my filters the wrong way round see if it's any better now? (Same link)

Project Filter = 
VAR EarliestVisibleDate = MIN ( 'Date'[Date] )
VAR LatestVisibleDate = MAX ( 'Date'[Date] )

VAR FilteredProjects = 
    FILTER (
        Project,
        Project[Finish] >= EarliestVisibleDate && Project[Start] <= LatestVisibleDate
    )

RETURN INT ( NOT ( ISEMPTY ( FilteredProjects ) ) )

Alternatively have a look at the second file on the same link. It uses power query to transform your table into a row per day per project:

This has the advantage of just being able to use a straight relationship.

Hope it gives you some ideas!

[Anonymous]

@bcdobbs 

I would have ideally implemented the first option, but seems to have a lot of limitations, especially when dealing with Months and multiple visuals. Eg. Projects that are Finishing before the end of the Month, but still need to be part of Active Projects.

However, the second example is working perfectly! There were too many columns in the actual table so I ended up creating a reference table with a bidirectional relationship for just the primary key and dates.

Thank you so much for your assistance!!

[bcdobbs]

No worries. You can further modify the filter on option 1 to include an OR statement (use || and brackets) to make it more inclusive.

Option 2 though is more flexible and much much faster on large datasets.