Filter if a Table if selected date falls between 2 date ranges

[MattScruggs]

I have the following set up. 

Tag       Start Date     End Date

1234	12/31/2009	1/21/2020
1235	3/8/2019	3/10/2019
1236	1/30/2019
1237	5/18/2008
1238	4/3/2020	10/5/2020
1239	12/3/2018	12/6/2018

I need to be able to create a set of filters for Month, Quarter, and Year that filter and show anything that was standing within this time frame. 

For example. 

If the filters are set to January, 2020, Then 1234,1236,1237, and 1238 should show. 

If the filters are set to May, 2020, Then 1236, 1237, and 1238 should show.

If the filters are set to Q4, 2020, Then 1236, 1237, and 1238 should show.

If the filters are set to December, 2019 Then 1234,1235,1236, and 1237 should show. 

I have had quite a bit of trouble getting this to work. 

I have a calender table with the following formula

Calendar = CALENDAR(

MIN(Maintable[Start Date]),

IF(

MAX(MainTable[Start Date]) > MAX(MainTable[End Date]),

MAX(MainTable[Start Date]),

MAX(MainTable[End Date]

)

)

)

I want to use this calender table for the filters. 

[AlB · Accepted solution]

@MattScruggs 

I'm posting here as well the response discussed by PM

I filtered out the blanks on build date (visual filter).

The updated, simplified code for the measure is:

Show measure V3 = 
VAR minPeriod_ = MIN ( Calendar[Date] )
VAR maxPeriod_ = MAX ( Calendar[Date] )
VAR currentStart_ = CALCULATE( SELECTEDVALUE ( Table1[ActualBuildDate] ), ALL('Calendar'))
VAR currentEnd_ = CALCULATE( SELECTEDVALUE ( Table1[ActualDismantleDate]), ALL('Calendar'))
RETURN
    IF (
        NOT (
            currentStart_ > maxPeriod_
                || ( currentEnd_ < minPeriod_ && NOT ISBLANK ( currentEnd_ ) )
        ),
        1,
        0
    )

1. If you place this measure in the table visual it should work. Do check that the results are as expected. However, you need to keep the measure in the visual. See it on page SCAFF.Monthly (2)

2. However, taking the measure off the visual and placing it as visual filter as we had done earlier will not work because of the Calendar columns you are using in the slicer. In this case the rows shown will be the ones shown in point 1 except those for which ActualBuildDate falls outside the selected period in the slicers. This is because of the relationship between Calendar[Date] and Table1[ActualDismantleDate] See it on page SCAFF.Monthly (3)

So if the solution on SCAFF.Monthly (2) is not good enough and you want the measure outside the visual, you have 2 options:

  a) Delete (or deactivate) the Calendar[Date] and Table1[ActualDismantleDate] relationship.

  b) Build an auxiliary date table that you'll use for the slicers and won't interfere with the visual. In this case the measure above will need to be adapted a bit

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@MattScruggs

I meant this alternative:

Show measure =
VAR minPeriod_ =
    MIN ( CalendarT[Date] )
VAR maxPeriod_ =
    MAX ( CalendarT[Date] )
VAR currentStart_ =
    SELECTEDVALUE ( Table1[Start Date] )
VAR currentEnd_ =
    SELECTEDVALUE ( Table1[EndDate] )
RETURN
    IF (
        NOT (
            currentStart_ > maxPeriod_
                || ( currentEnd_ < maxPeriod_ && NOT ISBLANK ( currentEnd_ ) )
        ),
        1,
        0
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@MattScruggs 

If this does not work, I'd need the pbix (or a mock that reproduces the issue). It owuld have been quicker to do it the "traditional" way, just checking the dates boundaries

Show measure =
VAR minPeriod_ =
    MIN ( CalendarT[Date] )
VAR maxPeriod_ =
    MAX ( CalendarT[Date] )
VAR currentStart_ =
    SELECTEDVALUE ( Table1[Start Date] )
VAR currentEnd_ =
    VAR aux_ =
        SELECTEDVALUE ( Table1[EndDate], 0 )
    RETURN
        IF ( NOT ISBLANK ( aux_ ), aux_, maxPeriod_ )
RETURN
    IF (
        ISFILTERED ( Table1[Tag] ),
        IF (
            COUNTROWS (
                INTERSECT (
                    GENERATESERIES ( minPeriod_, maxPeriod_ ),
                    GENERATESERIES ( currentStart_, currentEnd_ )
                )
            ) > 0,
            1,
            0
        ),
        0
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@MattScruggs 

Yeah, it's because of the SELECTEDVALUE() at the total

Show measure =
VAR minPeriod_ =
    MIN ( CalendarT[Date] )
VAR maxPeriod_ =
    MAX ( CalendarT[Date] )
VAR currentStart_ =
    SELECTEDVALUE ( Table1[Start Date], 0 )
VAR currentEnd_ =
    SELECTEDVALUE ( Table1[End Date], -1 )
RETURN
    IF (
        COUNTROWS (
            INTERSECT (
                GENERATESERIES ( minPeriod_, maxPeriod_ ),
                GENERATESERIES ( currentStart_, currentEnd_ )
            )
        ) > 0,
        1,
        0
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MattScruggs]

Show measure =

VAR minPeriod_ =

MIN ( Calendar[Date] )

VAR maxPeriod_ =

MAX ( Calendar[Date] )

VAR currentStart_ =

SELECTEDVALUE ( '1_Overview'[ActualBuildDate], 0 )

VAR currentEnd_ =

SELECTEDVALUE ( '1_Overview'[ActualDismantleDate], -1 )

RETURN

IF (

COUNTROWS (

INTERSECT (

GENERATESERIES ( minPeriod_, maxPeriod_ ),

GENERATESERIES ( currentStart_, currentEnd_ )

)

) > 0,

1,

0

)

This is the formula as I have it typed in exactly. I cant seem to pin down the issue. Its still saying Generateseries cant be blank. 

[MattScruggs]

It is giving me an error, staying that GENERATESERIES cannot be blank. The End dates have the potential to be blank. Could this be causing the error? 

[AlB]

Hi @MattScruggs 

First off, make sure you create a well-formed calendar table, with full years or you might have problems with time intelligence down the line. You can create a measure and apply it as filter to the table visual:

Show measure =
VAR minPeriod_ =
    MIN ( CalendarT[Date] )
VAR maxPeriod_ =
    MAX ( CalendarT[Date] )
VAR currentStart_ =
    SELECTEDVALUE ( Table1[Start Date] )
VAR currentEnd_ =
    SELECTEDVALUE ( Table1[End Date] )
RETURN
    IF (
        COUNTROWS (
            INTERSECT (
                GENERATESERIES ( minPeriod_, maxPeriod_ ),
                GENERATESERIES ( currentStart_, currentEnd_ )
            )
        ) > 0,
        1,
        0
    )

 Set the measure as a visual filter and  choose to Show when value is 1. I am assuming here that you have the table visual as shown above (with those 3 columns). In that, applying the measure as filter will result in the table showing the rows that have one day within the period selected in the slicers.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers