Forum Discussion

amandabus21's avatar
amandabus21
Icon for Helper V rankHelper V
3 years ago
Solved

Date and Time Filters

Hi, 

 

Im having trouble filtering between a certain range of dates, and then after, filtering a certain range of hours.

 

I need all the incidents from 3/1/22 and 9/30/22. (That I can do with the sclicer)

 

Next, I need incidents only from 6:00 PM - 7:00AM between 3/1/22 - 9/30/22.

 

 

Thank you!

  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    amandabus21 Meant to be used in the Filters pane actually. The intent was to slice using your TimeTable. However, you may need two time tables, one to set a min and one to set a max. And, if you need to go from one day to the next (PM -> AM) then you will probably need to tweak the measure selector accordingly.

     

     

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    amandabus21 Construct a TimeTable for your Time slicer:

     

    HourTable = 
SELECTCOLUMNS(
    ADDCOLUMNS(
        GENERATESERIES(1,24,1),
        "Hour",TIME([Value],0,0)
    ),
    "Hour",[Hour]
)

     

    Then create a Complex Selector like:

     

    Time Selector Measure =
  VAR __Opened = MAX('Table'[Date Opened])
  VAR __MaxTime = MAX('TimeTable'[Hour])
  VAR __MinTime = MIN('TimeTable'[Hour])
  VAR __Time = __Opened - TRUNC(__Opened)
RETURN
  SWITCH(TRUE(),
    __Time >= __MaxTime, 1
    __Time <= __MinTime && __Time >= 0, 1,
    0
  )