How to filter a report using time

Hi jdb9294 

In Edit queries, create two tables

Date table, code in Advanced editor

let 
    startdate=#date(2019,1,1),
    today=DateTime.Date(DateTime.LocalNow()),
    length=Duration.Days(today-startdate),  
    Source = List.Dates(startdate,length,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}})
in
    #"Renamed Columns"

Time table

let
    Source = List.Times(#time(0,0,0),24,#duration(0,0,60,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "time"}}),
    #"Inserted Hour" = Table.AddColumn(#"Renamed Columns", "Hour", each Time.Hour([time]), Int64.Type)
in
    #"Inserted Hour"

Close&&apply

Create a measure and add it into the visual level filter

Measure =
IF (
    MIN ( 'Date'[Date] ) < MAX ( 'Table'[call date] )
        && MAX ( 'Table'[call date] ) < MAX ( 'Date'[Date] ),
    1,
    IF (
        (
            MAX ( 'Table'[call date] ) = MIN ( 'Date'[Date] )
                && HOUR ( MAX ( 'Table'[call time] ) ) >= MIN ( 'Time'[Hour] )
        )
            || (
                MAX ( 'Table'[call date] ) = MAX ( 'Date'[Date] )
                    && HOUR ( MAX ( 'Table'[call time] ) ) <= MAX ( 'Time'[Hour] )
            ),
        1,
        0
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.