Filter start and finish date using single date slicer

[NZCraig]

Hi,

I am trying to come up with a filter that shows only table rows that have a start and finish date between the date slicer range on a page. I have found several solutions on the forum that get me close, but I am sure I'm having 'one of those days' for about a week now, and can't figure out the final part. I can use the expression below to get me close, but it shows all rows where the dates overlap the slicer range, and I need it to show exclusively within the slicer range.

Any help would be very much appreciated!!!

Craig

Current measure which shows overlapping dates

Date Included =

IF (

FIRSTNONBLANK ( WO[schedstart], 1 ) <= MAX ( 'Date'[Date] ) &&

FIRSTNONBLANK( WO[schedfinish], 1 ) >= MIN ( 'Date'[Date] ),

"Include",

"Exclude"

)

Expected result for date range 07 SEP 20 - 13 SEP 20

WO Number	WO schedstart	WO schedfinish	Date Included
Job#1	03/09/20	07/09/20	Excluded
Job#2	08/09/20	10/09/20	Included
Job#3	08/09/20	23/09/20	Excluded
Job#4	09/09/20	09/09/20	Included
Job#5	10/09/20	15/09/20	Excluded
Job#6	11/09/20	17/09/20	Excluded
Job#7	11/09/20	30/11/20	Excluded
Job#8	12/09/20	12/09/20	Included
Job#9	12/09/20	12/09/20	Included
Job#10	13/09/20	14/09/20	Excluded

[Greg_Deckler]

@NZCraig Just to add what @DataInsights great reponse, this solution assumes a disconnected date table, unless I am mistaken, so no relationship between your date table that fuels your slicer and your fact table.

[NZCraig]

Thanks @DataInsights  and @Greg_Deckler , I am indeed using a related date table.

I think typing the question out helped me to reframe my thinking, and I've come up with the follwing, which seems to work, but I'll need to play a bit more and see if I can break it.

Date Included =

IF (AND(FIRSTNONBLANK ( WO[schedstart], 1 ) <= MAX ( 'Date'[Date] ),FIRSTNONBLANK ( WO[schedstart], 1 ) >= MIN ( 'Date'[Date] )) &&

    AND(FIRSTNONBLANK( WO[schedfinish], 1 ) >= MIN ( 'Date'[Date] ),FIRSTNONBLANK( WO[schedfinish], 1 ) <= MAX ( 'Date'[Date] )),

    "Include",

    "Exclude"

)

[DataInsights]

@NZCraig, try this measure:

Date Included =
VAR vMinDate =
    MINX ( ALLSELECTED ( 'Date' ), 'Date'[Date] )
VAR vMaxDate =
    MAXX ( ALLSELECTED ( 'Date' ), 'Date'[Date] )
VAR vResult =
    IF (
        MAX ( WorkOrders[WO Sched Start] ) >= vMinDate
            && MAX ( WorkOrders[WO Sched Finish] ) <= vMaxDate,
        "Included",
        "Excluded"
    )
RETURN
    vResult

The date slicer should be based on the date table. You can add a filter to the table visual that shows only rows where Date Included = "Included".