Forum Discussion

MattScruggs's avatar
MattScruggs
Frequent Visitor
5 years ago
Solved

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

I have the following set up. 

Tag       Start Date     End Date

123412/31/20091/21/2020
12353/8/20193/10/2019
12361/30/2019 
12375/18/2008 
12384/3/202010/5/2020
123912/3/201812/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's avatar
    AlB
    5 years ago

    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 

     

7 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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 

     

     

     

     

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      MattScruggs
      Frequent Visitor
      5 years ago
      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's avatar
    MattScruggs
    Frequent Visitor
    5 years ago

    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's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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