Filtering entire report based on a in between time?

[thmonte]

I am attempting to create a report that all filters will react to and I am having some trouble.

My data set has two columns to pull off of -

start date/time & end date/time

I want to build something that I can use a date and time slicer to see if this record was "active" within this range

lets say I set my slicer to start date 1/9/2018 2:00PM to 1/10/2018 1:00PM, I would expect rows 1 & 2 to be the only two records in the report.  This goes for all visuals - so lets say I did a visual of distinct count of numbers in the column I would get 2.

I tried something very similar but it never works the way I intend it to.

I have a date table that has no relationship to my main data table.

I wrote a measure that gets a count of rows on the main table and returns a number which is fitlered based on the min/max from the data table (which has its own slicer).  I then put a filter on the visuals in the report for that mesaure and if greater than 0.  It kind of works but not totally how I expect.

#	start date	end date
1	1/9/2018 10:00 AM	1/10/2018 8:00 AM
2	1/10/2018 10:00 AM	1/11/2018 8:00 AM
3	1/11/2018 10:00 AM	1/12/2018 8:00 AM
4	1/12/2018 10:00 AM	1/13/2018 8:00 AM
5	1/13/2018 10:00 AM	1/14/2018 8:00 AM
6	1/14/2018 10:00 AM	1/15/2018 8:00 AM
7	1/15/2018 10:00 AM	1/16/2018 8:00 AM
8	1/16/2018 10:00 AM	1/17/2018 8:00 AM
9	1/17/2018 10:00 AM	1/18/2018 8:00 AM

[Anonymous]

Measure =
CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( Table1, Table1[Startdate] > MIN ( 'Table'[Date] ) )
)

THIS Should work

[thmonte]

Will this work on all visuals?

[Anonymous]

Yes, This will work on all visuals.

[parry2k]

@thmonte  do you want slicer date range against start date / time or also end date /time?

[thmonte]

Now that I am thinking about it..

Maybe the slicer should just be a single date/time

I would expect that if I select 1/1/2019 8:00AM

I would expect any records where the slicer date falls between the start date/end date to be filtered out on the report.

[v-shex-msft]

Hi @thmonte ,

Do you means filter records which in selected range? If this is a case, you can't direct use slicer to achieve this.

You need to add new slicer with date field who not has relationship to original tables. Then write a measure to compare current row contents(start and datetime) with selected date and return result tag, drag this measure to visual level filter to filter non matched records.

Tag Measure =
VAR _start =
    MAX ( Table[Start] )
VAR _end =
    MAX ( Table[End] )
RETURN
    IF (
        _start IN ALLSELECTED ( Date[Date] )
            || _end IN ALLSELECTED ( Date[Date] ),
        "N",
        "Y"
    )

Regards,

Xiaoxin Sheng