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 th...
  • 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.

     

     

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

amandabus21 As a table. In Report view in the Desktop, choose the Modeling tab and then New table and paste in that formula.

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

    Hi Greg, thanks for your help. Greg_Deckler 

     

    I was able to create the table but now when I create the measure I am getting a syntax error. 

     

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

      amandabus21 Whoops, missed a comma after the first SWITCH condition:

      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
  )
      • amandabus21's avatar
        amandabus21
        Icon for Helper V rankHelper V
        3 years ago

        Greg_Deckler thank you. 

         

        Now how can I use that measure to filter the time between 6PM - 7AM? 

         

        Should I make a new column or is it supposed to be used a slicer?