Forum Discussion

jdb9294's avatar
jdb9294
Frequent Visitor
6 years ago
Solved

How to filter a report using time

I have a table that includes incidents and captures the date and time of the incident.

 

I have a report that counts various things related to that incident.

 

I want to use a slicer to filter this report by date and time.

 

For example, I want to see all the incidents that happened between 1/1/2019 0800 to 01/07/2019 1600.  I would want all incidents that occurred from that start date\time to the end.  So an incident on 1/2/2019 0700 would be included.

 

I have included a screenshot to show how my data is currently stored.

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

6 Replies

    • jdb9294's avatar
      jdb9294
      Frequent Visitor
      6 years ago

      Thanks for the link.  I have the table created and related to my call time, but I am still not sure how to get a slider to do what I want it to do here...

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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.