Advanced Date Filtering for Active rows at any date during the period

[bridgwan]

I need a method to dynamically filter two columns (Start Date and End Date) to include active rows for one or more dates in a user defined range. The four scenarios for rows to be included are:

1. Row Start Date before the Range Start Date and the Row End Date before the Range End Date

2. Row Start Date between Range Start and End Dates and the Row End Date after Range End Date

3. Row Start Date after Range Start Date and Row End Date before Range End Date

4. Row Start Date before Range Start Date and Row End Date after Range End Date

Example Data:

Dynamic Date Range: 1st Oct 2021 to 31st Oct 2021

1. Start Date: 1st Sept 2021 - End Date: 17th Oct 2021

2. Start Date: 12th Oct 2021 - End Date: 10th December 2021

3. Start Date: 6th Oct 2021 - End Date: 23rd Oct 2021

4. Start Date: 15 Aug 2021 - End Date: 20 Nov 2021

Any help would be appreciated.

[bridgwan]

Thanks. I will try this out

[Anonymous]

Hi @bridgwan ,

According to my understanding, you want to dynamically set a flag or filter data by some conditions such as :

period 1 for :  Row Start Date < Range Start Date and the Row End Date < Range End Date

period 2 for :  Row Start Date > Range Start and < End Dates and the Row End Date > Range End Date

period 3 for :  Row Start Date > Range Start Date and Row End Date < Range End Date

period 4 for :  Row Start Date < Range Start Date and Row End Date > Range End Date

Based on my test, if you apply Advanced filtering to visuals, it could not be dynamically, everytime you need to change date range.

So I suggest you need to create a new calendar table for slicer which used for "dynamic":

Calendar = CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End Date])) 

Then use the following formula to create a measure:

Flag =
VAR _rangeStart =
    MIN ( 'Calendar'[Date] )
VAR _rangeEnd =
    MAX ( 'Calendar'[Date] )
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Table'[Start Date] ) < _rangeStart
            && MAX ( 'Table'[End Date] ) < _rangeEnd, "period 1",
        MAX ( 'Table'[Start Date] ) > _rangeStart
            && MAX ( 'Table'[Start Date] ) < _rangeEnd
            && MAX ( 'Table'[End Date] ) > _rangeEnd, "period 2",
        MAX ( 'Table'[Start Date] ) > _rangeStart
            && MAX ( 'Table'[End Date] ) < _rangeEnd, "period 3",
        MAX ( 'Table'[Start Date] ) < _rangeStart
            && MAX ( 'Table'[End Date] ) > _rangeEnd, "period 4"
    )

You could use Multi-row card visual to display different period like this:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bridgwan]

Thanks for your reply. I suspect that I did not word my query clearly enough.

I have a database table with two date columns - Start Date and End Date. I need create a splicer to filter the rows in a table visualisation by month where at least one of the dates in the range is within the selected month. 

Month Selected in the Slicer: Oct 2021

The rows that should be available in the table will included all of the following examples

1. Start Date: 1st Sept 2021 - End Date: 17th Oct 2021

2. Start Date: 12th Oct 2021 - End Date: 10th December 2021

3. Start Date: 6th Oct 2021 - End Date: 23rd Oct 2021

4. Start Date: 15 Aug 2021 - End Date: 20 Nov 2021

[Anonymous · Accepted solution]

Hi @bridgwan ,

Sorry for my misunderstanding, you may try this measure:

Measure =
VAR _sele =
    YEAR ( MAX ( 'Calendar'[Date] ) ) * 100
        + MONTH ( MAX ( 'Calendar'[Date] ) )
RETURN
    IF (
        _sele
            >= YEAR ( MAX ( 'Table'[Start Date] ) ) * 100
                + MONTH ( MAX ( 'Table'[Start Date] ) )
            || _sele
                <= YEAR ( MAX ( 'Table'[End Date] ) ) * 100
                    + MONTH ( MAX ( 'Table'[End Date] ) ),
        1
    )

Then apply the measure to filter pane(set as "is 1"), the final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.