How to double filter using two date?

[mork]

I have a table that has the bellow fields:

Project Name

Start Date

Completion Date

I want to have a visual table that has those three fields, this is quite easy to do.

But I also want to filter that table by both the Start Date and the Completion Date. I want to be able to select a date range and the table will show me all the projects that either the Start or the Completion Date are within the date range I selected.

I tried creating a datetable and relating both the Start Date and the Completion Date with that datetable but powerbi allows only one of the relationships to be active.

How can I achieve what I want?

[Framet]

Hi, 

You could create the create the date table but without a relationship at all. 

That way you could adjust the filter context of the measure accordingly.

For example to get a count of all projects of which at least one day crosses your date range filtered on the data table:

ProjectsCrossingDates :=
CALCULATE (
    COUNTROWS ( 'ProjectTable' ),
    FILTER (
        'ProjectTable',
        [Start Date] <= MAX ( DateTable[Date] )
            && [Completion Date] >= MIN ( DateTable[Date] )
    )
)

Note that having no active relationship to date requires that all measures requring time intelligence need to manually filtered in the way in which you expect them to react to the users date selections.

Hope this helps.

Thomas

[Baskar]

1. For this case u have to duplicate the table and create two relationship one for start Date and another one for completion date.

2. one more way is using Userelationship  function in calculations .