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 -  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...
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!

bcdobbs's avatar
bcdobbs
Icon for Community Champion rankCommunity 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
      Icon for Community Champion rankCommunity 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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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!!