Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter active projects based on selected date range

Hey all,

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

TeamProjectStartFinish
Team A110/10/20213/2/2022
Team A21/1/20222/1/2022
Team B38/4/20215/1/2022
Team B42/2/20222/20/2022
Team B51/5/20224/10/2022
Team C63/8/20224/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!!

Need Help
Tips and Tricks
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    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!

5 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        bcdobbs
        Community Champion
        4 years ago

        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!